none
Login details in Power Query

    Question

  • Hi all - 

    How can we get the details of login user dynamically in power query advanced editor.

    Here is the scenario that I'm trying to solve. 

    I have the list of employees and their details extracted from a database and published thru power query. I will share the query with all the employees that I have extracted. Now I want the employee to see his/her specific details only when they login and run the query that I shared.

    We can add a filter to our query in the advanced query editor, but the filter value should be  the logged on user. I need to know how can we get the logged on user details in the power query advanced editor.

    Thanks for the help,

    Deva.

    Friday, November 14, 2014 7:55 PM

Answers

All replies

  • I'm afraid there isn't any way to do this today.
    Friday, November 14, 2014 8:07 PM
    Owner
  • I'm sure you're well aware of the requirement, but can I add my vote for a function that returns the Windows username of the person currently running the query? It would be very useful...

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Wednesday, November 19, 2014 11:32 AM
  • 1.5 years later.... 

    I'm using power BI and using live connection to SQL server as a data source and need to filter the data by logged in username.  Is there now a way to get the username from within Power Query Advanced Editor?

    Marc

    Monday, February 22, 2016 5:58 PM
  • Only way I'm aware of is to create a measure in PowerPivot: USERNAME()

    Return this in a pivottable or cubefunction and it will show the current user. In order to pass the field from the pivottable to Power Query you have to give it a name - otherwise it would return error.


    Imke Feldmann TheBIccountant.com

    Monday, February 22, 2016 6:23 PM
    Moderator
  • Thanks Imke,

    I'm a bit of a newbie when in comes to Power Query and Power Pivot.  Can you point me in the direction of a good example for the syntax for your solution  My current query looks like :

    let
        Source = Sql.Databases("<database IP>\SQLEXPRESS"),
        Username = "loggedInUserName@domain.com",
        Insight02 = Source{[Name="Insight02"]}[Data],
        dbo_view_Applications = Insight02{[Schema="dbo",Item="view_Applications"]}[Data],
        #"Filtered Rows" = Table.SelectRows(dbo_view_Applications, each ([domain_user_name] = Username))
    in
        #"Filtered Rows"


    Thanks in advance,

    Marc]

    Monday, February 22, 2016 6:40 PM
  • Yes, query looking a bit strange.

    I've reordered it, so now the first 3 rows belong together and return the data from the SQL server unfiltered (you should be able to see the results when clicking on each step in the editor). Then comes the Username-step which is probably hardcoded at the moment? This value is then used as the filter argument for the last step. [domain_user_name] being the name of the column from the SQL-table and Username the name of the step that is also a variable name.

    Assuming that the USERNAME will only return the “loggedInUserName” and not the @domain.com-part, your query should look like this (otherwise just delete the domain-part in the code below):

    let
        Source = Sql.Databases("<database IP>\SQLEXPRESS"),
        Insight02 = Source{[Name="Insight02"]}[Data],
        dbo_view_Applications = Insight02{[Schema="dbo",Item="view_Applications"]}[Data],

        Username = Excel.CurrentWorkbook(){[Name="UsernameFromExcel"]}[Content][Column1]{0}&“@domain.com",

        #"Filtered Rows" = Table.SelectRows(dbo_view_Applications, each ([domain_user_name] = Username))

    in
        #"Filtered Rows"

    UsernameFromExcel being the name that you've assigned to the measure you've retrieved from PP.

    You might need to enable “Fast Combine”: https://support.office.com/en-us/article/Privacy-levels-Power-Query-cc3ede4d-359e-4b28-bc72-9bee7900b540#__toc381720831


    Imke Feldmann TheBIccountant.com

    Monday, February 22, 2016 7:05 PM
    Moderator
  • Thanks again Inke,

       Yes, my original query worked but the username was hardcoded as a place holder for a function call that would return the username.  I'm using Power BI desktop to create  datasets and reports that will be published and that will only show data that is allowed for the current logged in user. I'm NOT currently using EXCEL at all.  Will your method work for me or is it only applicable to solutions using Excel with power query?

    Marc


    • Edited by marcLabonte Monday, February 22, 2016 7:34 PM
    Monday, February 22, 2016 7:33 PM
  • Sorry Marc, my solution will only work in Excel.

    No idea on how to solve this in PBI at the moment.


    Imke Feldmann TheBIccountant.com

    Monday, February 22, 2016 8:20 PM
    Moderator
  • I don't know how reliable this is but I'm finding out the current user based on Date Accessed field from the C:/Users folder.

    Just putting this here if anyone finds it useful.

    let
        Users = Folder.Contents("C:/Users"),
        CurrentUser = Table.FirstN(Table.Sort(Users,{{"Date accessed", Order.Descending}}),1)[Name]{0}
    in
        CurrentUser

    Wednesday, March 15, 2017 4:36 PM
  • Come on Igor, you know that this is totally awesome :-)

    Dynamic RLS (without the security) but to conveniently filter your imports according to the current user is very attractive !!


    Imke Feldmann TheBIccountant.com



    Wednesday, March 15, 2017 4:57 PM
    Moderator