Power Query data filtration based on logged-in user

[SanketSk]

Dear Power Query Experts,

I am writing to ask for your help with Power Query. I have a large dataset stored on SharePoint in different Excel files on a quarterly basis. This data is from various branches across India.

Since we don't want to share the entire data to everyone, we use Excel Power Query to compile branch level details for analysis and discussion with my team, so we end up with multiple files workbooks with multiple queries.

The query here is that we can set up a power query to check the user id with which the person is logged in to excel and filter data.

Sharing the current matrix of users with their responsibility, please guide if we can create a Excel Power Query that returns data based on the responsibility of logged-in user.

Branch Manager
Name	Responsible for	Region
Aaditya@work.com	Hyderabad	South
Aayu@work.com	Patna	East
Bhuv@work.com	Delhi	North
Brij@work.com	Ahmedabad	West
Chirag@work.com	Vadodara	West
Daiwik@work.com	Bangalore	South
Dipal@work.com	Indore	West
Eashan@work.com	Bhopal	West
Ehan@work.com	Mumbai	West
Grishm@work.com	Nagpur	West
Hans@work.com	Jaipur	North
Hiral@work.com	Chennai	South
Ilesh@work.com	Lucknow	North
Jagrav@work.com	Kolkata	East
Regional Manager
Name	Responsible for	Region
Fanish@work.com	Esat	Esat
Gyan@work.com	North	North
Chahal@work.com	West	West
Ichaa@work.com	South	South
Country Manager
Name	Responsible for	Region
Anil@work.com	All India

I appreciate your help and guidance on this. If you have any YouTube video series or other resources that address this query, please share them with me.

Thank you for your time and expertise.

Thanks,

Sanket

[Anonymous]

Two ways: you can query the folder C:\users and get the latest Date Modified folder, which is usually the current username. 

A better way is to add a table to the data model, or if you do not have a data model, add a simple query named "Today" (like Column1 and the DateTime.LocalNow()) in Power Query and load it to the data model (Connection only, add to data model). On that data model table, add the PPUser = USERNAME function to get the current user. Close and choose (Connection Only, Load to Data Model). Make sure that the query properties for this query are set to "Refresh data when opening the file" .Now we can use a cube formula on an Excel sheet to retrieve the username from the data model:

= CUBEVALUE("ThisWorkbookDataModel","[Measures].[PPUser]")

Make this cell a named range (like "PPUsername"). Now go to the query editor, and add a new query

= Excel.CurrentWorkbook(){[Name="PPUsername"]}[Content]

Now you have domain/user in a Power Query table that you can use to filter a column. 

--Nate

[ChielFaber]

Didn't test this myself but for a local file you could try:

https://thebipower.fr/index.php/2020/03/04/get-username-function-in-power-query/

I haven't tested it myself.  If you create a report in PowerBI you could use the DAX function Username().