Power Query with SharePoint list

[Anonymous]

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.

[parry2k]

@Anonymous you need to set up the privacy level since you combine two different data sources, SQL and Sharepoint.

✨ Follow us on LinkedIn and  to our YouTube channel

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.

[parry2k]

@Anonymous change as below:

"select region1,user,name,identify from tablename where user in (" & 
Text.Combine(YourUserTable[UserColumn],"," ) & ") order by user"

✨ Follow us on LinkedIn and  to our YouTube channel

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.

[Anonymous]

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.

[parry2k]

@Anonymous can you share the script where you are manually updating the user which needs replacement using table from Microsoft list.

✨ Follow us on LinkedIn and  to our YouTube channel

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.

[Anonymous]

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.

[Anonymous · Accepted 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.