Power Query - Filter Data by logged in User

Warning... This article is a bit techy....

Probably quite a narrow audience for this post but it's something that cropped up at a training session and was another interesting option of what Power Query can do. Plus I can see how this could be quite attractive in simple scenarios where you'd like to automatically refresh and filter the source data based on the logged in user.

I can't remember where I originally saw this solution but I also googled it and found it here https://querypower.com/2017/04/03/4-ways-to-get-username-in-power-query/

I've not used this approach in real life (let me know if you have) so just beware of reliability....

The screenshot shows I have a blue table of data (Table1) and after applying a filter for the logged in user I end up with the green table (Filtered Output).

Here's the code to create the Parameter for Current User

let

    Users = Folder.Contents ("C:/Users"),
    #"Filtered Rows" = Table.SelectRows(Users, let latest = List.Max(Users[Date accessed]) in each [Date accessed] = latest),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name"}),
    List = #"Removed Other Columns"[Name],
    #"Current User" = List{0}
in
    #"Current User"

Here's the code for the Filtered Output table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Department", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([User] = CurrentUser))
in
    #"Filtered Rows"

Until Excel has the option to lock down the query window and prevent people editing the query (vote here) this isn't a secure way of preventing people from seeing other data but could be an efficiency / file size thing.

Let me know if anyone has used this in real life and come across any issues.

Wyn

EXCEL MVP