Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
Apologies if this has been covered elsewhere but I am getting into territory with Power Query that is a little beyond me at the moment. We are working with Power Query (Import) with different where conditions. We have anothere share point list with all user names. How do we supply the sharepoint list values to Power Query where condition. We are usign sharepoint list because the list of user names may vary day by day. Currently we are copying user names manually and pasting in Power Query and refreshing reports. We wated to automate this process. Please advise if there any possibility to achieve this.
Sincerely,
Krishna.
Solved! Go to Solution.
Sure, below is the query we are using as part of Direct (import option) which is very simple query.
select region1,user,name,identify from tablename where user in
('User1','User2','User3') order by user
We have a sharepoint list which holds all the User details, so trying to map Sharepoint file in query so query refer the sharepoint list (part of where condition) when ever we refresh it, so we no need to open AdvancedEditor and update where condition. For now we are updating user list manually in AdvancedEditor updating User details manually but we wanted to get rid of it.
Sincerely,
Krishna.
@Anonymous you need to set up the privacy level since you combine two different data sources, SQL and Sharepoint.
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous change as below:
"select region1,user,name,identify from tablename where user in (" &
Text.Combine(YourUserTable[UserColumn],"," ) & ") order by user"
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I followed the steps but some how it is erturning error saying
Formula.Firewall: Query 'Query2' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Sincerely,
Krishna.
@Anonymous can you share the script where you are manually updating the user which needs replacement using table from Microsoft list.
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Below the script I am trying to use
let
Source = Sql.Database("dbdetails", [Query="select name from table where name in
(" & Text.Combine(BankCIList[Bank_CI_Name],"," ) & ")"])
in
Source
Wheer BankCIList is the sharepoint sourced list imported into power bi as separate query.
Thank you,
Krishan.
Sure, below is the query we are using as part of Direct (import option) which is very simple query.
select region1,user,name,identify from tablename where user in
('User1','User2','User3') order by user
We have a sharepoint list which holds all the User details, so trying to map Sharepoint file in query so query refer the sharepoint list (part of where condition) when ever we refresh it, so we no need to open AdvancedEditor and update where condition. For now we are updating user list manually in AdvancedEditor updating User details manually but we wanted to get rid of it.
Sincerely,
Krishna.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |