Forum Discussion
Helper IHow to retrieve userprincipalname in Power query editor
I have a sql query which has to be filtered based on the userprincipalname I don't see any option to filter by userprincipalname in Power query editor
Scenario if the userprincipalname storeid is 'NA' I want to seee all the users under the storeid 'NA'
so I am using a sql query which can get the list of users who share the same id as selected user but the catch is in PowerBI I want to filter by userprincipalname unfortunately I don't see any function that is equivalent to userprincipalname on power query editor how should I dynamically pass in the userprincipalname to the query
Hi Harineeeeeeee ,
Thanking for reaching out to fabric community forum.
nilendraFabric Thanks for your prompt response. In additon to that :- You need to retrieve the User Principal Name (UPN) from Active Directory and map it to its corresponding StoreID. This mapping should be stored in a security table within your database. Ensure that this table is updated regularly to keep all user-store associations current.
- Once the data is stored in the security table, you can load it into Power Query for transformations, making it easier to query and analyze the data through a user-friendly interface.
- Next, create a function that retrieves the StoreID based on the provided User Principal Name as a parameter (@parameter). Use this parameter to filter your SQL query.
- However this function would need to be invoked from power query every time you need to query a user.
If our response or a community member’s response addressed your query, please mark it as 'Accept Answer' and click 'Yes' if you found it helpful.
If you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!
8 Replies
- nilendraFabric
Super User
Hello Harineeeeeeee
Power Query does not have access to runtime user context, you cannot directly retrieve or pass USERPRINCIPALNAME() into your SQL queries from Power Query. The recommended solution is to use DAX—via measures or row-level security—to handle user-specific filtering after data is loaded.
There is no native M function to retrieve the login user’s principal name. This is because Power Query processes data during refresh, independent of the interactive user context in the report
Check this discussion as well
If this is helpful please accept the answer and give kudos
HarineeeeeeeeThanks for reply but unfortunately this doesn't serve my purpose so I have to take the selected user that is userprincipalname and select the store id once I found my store id i ll remove the filters on userprincipalname and based on the found store id i will have to show all the users nilendraFabric
- nilendraFabric
Makes sense. But M language doesn't have userprincipalname concept.
- Harineeeeeeee
Any other approach would go with this logic? nilendraFabric
v-aatheequeCommunity Support
Hi Harineeeeeeee ,
Thanking for reaching out to fabric community forum.
nilendraFabric Thanks for your prompt response. In additon to that :- You need to retrieve the User Principal Name (UPN) from Active Directory and map it to its corresponding StoreID. This mapping should be stored in a security table within your database. Ensure that this table is updated regularly to keep all user-store associations current.
- Once the data is stored in the security table, you can load it into Power Query for transformations, making it easier to query and analyze the data through a user-friendly interface.
- Next, create a function that retrieves the StoreID based on the provided User Principal Name as a parameter (@parameter). Use this parameter to filter your SQL query.
- However this function would need to be invoked from power query every time you need to query a user.
If our response or a community member’s response addressed your query, please mark it as 'Accept Answer' and click 'Yes' if you found it helpful.
If you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!- v-aatheeque
Hi Harineeeeeeee ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us knowThank you.
