4 ways to get USERNAME in Power Query

Regardless of what are you requirements, be that 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 4 methods on getting the current username in Power BI. These methods rely on a variety of dependencies — R, NTFS, LocalDB and PowerShell, and come with their own advantages and disadvantages. Based on your setup you might find some of the methods more appropriate than the others, however they are not a universal replacement for a native 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.

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 of when a file/folder was moved, opened, or access 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. Which makes this method unreliable in certain scenarios. Nevertheless I’ve recommended this shortcut in the past, and it is still my preferred way of getting to msmdsrv.port.txt file.

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 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.

install

Make sure to select the version that is relevant to your setup as the connection strings for LocalDB have changed over the years

username

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 which 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 very easy to follow and adapt to the relevant scenario.

Once I have downloaded, installed and imported the module, I start an instance by using the commandlet Start-HTTPListener.

Start-httpListener

To get the current user in Power Query I make an HTTP call to localhost:8888 with system command whoami.

Back in the PowerShell console, the flag -verbose provides more details to what is happening behind the scenes:

whoami

Pros: This method can be expanded to sourcing the output from other system commands as well as PowerShell commandlets.

Cons: Introduces an additional dependency which 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. I would want Power BI to expose a functions for parsing the console output, this would help with accessing a lot of obsolete data sources as well as standard system commands. Or if we would like to keep only to system specific domain how about having a connector for WMI the same as SSIS does?

2 thoughts on “4 ways to get USERNAME in Power Query

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s