Forum Discussion
Power Query data filtration based on logged-in user
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 |
Hyderabad | South | |
Patna | East | |
Delhi | North | |
Ahmedabad | West | |
Vadodara | West | |
Bangalore | South | |
Indore | West | |
Bhopal | West | |
Mumbai | West | |
Nagpur | West | |
Jaipur | North | |
Chennai | South | |
Lucknow | North | |
Kolkata | East | |
Regional Manager | ||
Name | Responsible for | Region |
Esat | Esat | |
North | North | |
West | West | |
South | South | |
Country Manager | ||
Name | Responsible for | Region |
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
2 Replies
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().
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
