Get username function in Power Query with few lines of M code. For the moment, there is no native way to get the Windows username in M language. The username might be useful to filter a table or to get a file path in the ‘Documents’ folder.
The following post describes 4 ways to get the username in Power Query :
https://querypower.com/2017/04/03/4-ways-to-get-username-in-power-query/
Unfortunately, the simplest method (n°2) is not reliable because of NTFS delay as explained in the post.
So I developped a function using the same concept with some improvements.
How to get username function in Power Query
- In Power Query, add a blank query
- Then open the advanced editor and paste the code down below
- Rename the function
As the function requires no parameter, it is straight forward to use it
() =>
let
Source = Folder.Contents("C:\Users\"),
#"Expanded Attributes" = Table.ExpandRecordColumn(Source, "Attributes", {"Hidden", "Directory", "ChangeTime"}, {"Hidden", "Directory", "ChangeTime"}),
#"Filtered Directories not hidden" = Table.SelectRows(#"Expanded Attributes", each ([Directory] = true) and ([Hidden] = false)),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Filtered Directories not hidden", {"ChangeTime"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Name] <> "Public")),
#"Username" = #"Filtered Rows"{0}[Name]
in
#"Username"Limitation : it works well in the desktop but not sure it will work so good in the service depending on the way you refresh the data. For example, if you refresh data with a gateway, you might get the username of the Windows session which launched the refresh.
You may also have a look to Power BI assistant : https://thebipower.fr/index.php/power-bi-assistant/