username

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/

Custom analyser

Custom analyser

With ‘Custom analyser’ feature, Power BI Sidetools users can use external tools built by the…

Read More

9 Comments

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

    Reply

  3. Unfortunately this gives me a user, but not the current one.
    It seems to be the first one in the Users folder.
    Is there maybe something I’m doing wrong (copied the code 1:1 in advanced editor in Excel 2016) or another solution?

    Thank you!

    Reply

  4. Hi there, the username function works when I invoke it but how do I reference it in the file path. The function is called UserName_fx and I receive an error when I put it in the file path outlined below. I don’t know M so need assistance. Thanks

    The current path for my data source in the advanced editor:
    Source = Csv.Document(File.Contents(“C:\Users\UserName_fx \PowerBI Files\Customer.csv”),[Delimiter=”,”, Columns=45, Encoding=65001, QuoteStyle=QuoteStyle.None]),

    Reply

    1. Hello Maureen,
      You might try this :
      Source = Csv.Document(File.Contents(“C:\Users\" & UserName_fx & "\PowerBI Files\Customer.csv”),[Delimiter=”,”, Columns=45, Encoding=65001, QuoteStyle=QuoteStyle.None]),

      Reply

    2. because it’s a function, you can do it like this:
      Source = Csv.Document(File.Contents(“C:
      \Users\” & UserName_fx() & “\PowerBI Files\Customer.csv”),[Delimiter=”,”, Columns=45, Encoding=65001, QuoteStyle=QuoteStyle.None]),

      Reply

  5. I have multiple users on one PC so this method doesn’t work for me. So I found my solution. If you are a network administrator just add the command echo %username% >C:\Users\user.log to the NETLOGON bat command for the user and then just read the contents on each PC. I have the bat command run even when the PC is unlocked, so there is always a last user that unlocks the PC.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *