Regardless of your requirements, providing some user context during slicing of data or a filtering mechanism for data querying, having a function like USERNAME() in Power Query toolbox can greatly simplify your data mashup scripts. While trying to build a more robust approach for self referencing queries in M, I’ve managed to collect four methods for getting the current username in Power BI. These methods rely on a variety of dependencies — R, NTFS, LocalDB and PowerShell, and come with their advantages and disadvantages. Based on your setup, you might find some methods more appropriate than the others; however, they are not a universal replacement for a natively supported function like USERNAME().
1. R.Execute()
Once again, the Swiss Army knife of Power Query — R with its hook function R.Execute, never fails at extending Power BI capabilities. Here is an example of how I call the system command whoami via R function system2 and use its output as a data source in Power Query.
| let | |
| RScript = R.Execute("output <- read.table(text=system2(""whoami"", stdout=TRUE))"), | |
| output = RScript{[Name="output"]}[Value] | |
| in | |
| output |
Pros: The advantage of this approach is that it can be adapted to get the STOUT of other system commands as well.
Cons: It depends on calling R, which is currently supported only by Power BI Desktop.
2. NTFS
The output table of Folder.Contents in Power Query contains the field Access Date, which records the timestamp when a file/folder was moved, opened, or accessed by any other means. Assuming that the NTFS Access Date records for C:/Users sub-folders are up to date, then the folder with the latest Access Date should be the current user.
A note of caution here, NTFS might take up to an hour to update the file times, making this method unreliable in specific scenarios. Nevertheless, I’ve recommended this shortcut in the past, and it is still my preferred way of getting to msmdsrv.port.txt file.
| //Assumes current user is the one with latest [Date accessed] folder in C:\Users | |
| let | |
| Users = Folder.Contents("C:/Users"), | |
| CurrentUser = Table.FirstN(Table.Sort(Users,{{"Date accessed", Order.Descending}}),1)[Name]{0} | |
| in | |
| CurrentUser |
Pros: Simple
Cons: Unreliable as NTFS can take up to 1 hour to update the Access Date records of a folder. The Antivirus and other background processes can interfere with the correctness of Access Date ⇒ Current User. Also, the output of this function doesn’t contain the domain name.
3. LocalDB or any other SQL source
If you happen to have a SQL Server instance lying around or by some curious accident you’ve installed SQL Server Express with support for LocalDB, you can make a trivial SQL query to get SYSTEM_USER. You don’t even need an assigned database for that.
There is always the option of installing only LocalDB feature of SQL Server Express.
Make sure to select the version relevant to your setup as the connection strings for LocalDB have changed over the years.
| //LocalDB | |
| UserNameLocalDB = | |
| () => Sql.Database("(localdb)\v11.0","",[Query="SELECT SYSTEM_USER"]) | |
| //LocalDB from SQL Server 2016 Express | |
| UserNameLocalDB2016 = | |
| () => Sql.Database("(localdb)\MSSQLLocalDB","",[Query="SELECT SYSTEM_USER"]) | |
| //Local instance of SQL Server | |
| UserNameSQL = | |
| () => Sql.Database(".","",[Query="SELECT SYSTEM_USER"]) |
Pros: You might already have LocalDB installed.
Cons: Requires access to a SQL Server instance or installation of LocalDB.
4. HTTP API for Executing PowerShell Scripts
This method relies on a lightweight service that listens to port 8888 and executes PowerShell commands sent over HTTP. The PowerShell team has released this script/service as an alternative to the SOAP heavy Windows Remote Management; for more details, please read the associated blog post: Simple HTTP api for Executing PowerShell Scripts. The service itself is written in PowerShell, and the 300 lines of code are straightforward to follow and adapt to the relevant scenario.
Once I have downloaded, installed and imported the module, I start an instance using the commandlet Start-HTTPListener.
To get the current user in Power Query, I make an HTTP call to localhost:8888 with the system command whoami.
| // Based on running the simple HTTP API service from: | |
| // https://blogs.msdn.microsoft.com/powershell/2014/09/29/simple-http-api-for-executing-powershell-scripts/ | |
| let | |
| output = Json.Document(Web.Contents("http://localhost:8888/?command=whoami")) | |
| in | |
| output |
Back in the PowerShell console, the flag -verbose provides more details to what is happening behind the scenes:
Pros: This method can be expanded to sourcing the output from other system commands and PowerShell commandlets.
Cons: Introduces an additional dependency that might interfere with users’ execution policies. The PowerShell commandlet has to be running in the background.
IMAO
We shouldn’t be asking Power BI team to implement only small utilities like UserName() or CookBreakfast(). Instead, we should see the bigger problem and ask for a bigger hammer. For example, I would want Power BI to expose functions for parsing the console output; this would help with accessing a lot of obsolete data sources and standard system commands. Or if we would like to keep only to a system-specific domain, how about having a connector for WMI the same as SSIS does?
Query Power 
Hi Igor,
Nice findings!
“a functions for parsing the console output” – I think, this could raise a question about security issues
LikeLike
Thanks Maxim! R has system2() why Power Query should be limited in this regards?
LikeLike
Iam getting below popup message while loading data from alteryx to powerbi.
Iam using Publish to Powerbi connector.
Need admin approval
Alteryx Power BI Connector
Alteryx Power BI Connector needs permission to access resources in your organization that only an admin can grant. Please ask an admin to grant permission to this app before you can use it.
LikeLike
I liked the simplisity of the NTFS option however it triggered a query firewall error when I nested that query within other queries.
I was trying to create a PATH parameter that referenced username. So when I sync to dropbox and switch computers that I am working on my queries don’t all need to be manually updated to keep working.
Why Power Query is the forgotten step child of Power BI is frustrating. I thought R scripts were past general availability but still not available in PQ?? Maybe I am wrong but its been years.
LikeLike
I’m having this very same issue. It just doesn’t work for paths.
LikeLike
Thanks for the 1. R.Execute(). and as R now works on the Power BI Service. I have used this to implement an elegant security model.
LikeLike
Excellent! would love to hear more about this
LikeLike
Hi hugoberry
When I get a moment I shall. In process of finalising the implementation…
LikeLike
I have implemented RLS
LikeLike
I have implemented RLS in the ‘normal’ way.
That is by using the ‘Manage roles’ functionality.
I did pursue the use of the first item above 1. R.Execute().
We cannot schedule refreshes on datasets if they contain R scripts.
Anyway, when I hit the brick wall of the scheduled refreshes I implemented RLS as per:
http://www.fourmoo.com/2016/10/03/power-bi-dynamic-row-level-security-things-you-need-to-know-to-get-it-working/
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://applepark.co.uk/automatic-filtering-in-power-bi-reports/
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
John
NB In the Manage roles dialog i used this DAX:
UPPER([EmailAddress]) = UPPER(USERPRINCIPALNAME())
BTW The use of RLS works on Direct Query(Live Connection) and Import, with scheduled refreshes.
LikeLike
I guess the NTFS also only works on the Desktop, right?
LikeLike