Get username function in Power Query
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/
That worked like a charm! Thanks!
Love this function… Was trying to figure out how to just embed it into a single query, but not gotten very far.
I have been trying to make a “system information” query so that when someone runs a Power Query in Excel and an issue or question comes up about what is going on I can say go look at the “System Info Table”. Unfortunately all I have last refresh, with TZ, User Name,
Love to get file name/path/excel info
Especially would love “did a complete refresh of all queries occur without error”. Sometimes a results look good, but when you look at the list of queries there is an error in 1 or more.
Hello Alan,
Thank you for the feedback.
Sorry, I have no idea how to monitor the queries refresh in Excel. May be VBA ? May be use Reza Rad solution :
https://www.youtube.com/watch?v=iCumyrP3HSE
Regards