cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aexley Member
Member

Using a lookup in M in one query to set a parameter within another query

Ok.

 

I've discovered when using SharePoint lists as a data source that all reference is thorugh the GUID for the site and then the GUID for the lists.

 

This poses some fun when moving between a development source to a production source as I have to manually go in to the queries and change the GUIDs for each list.

 

As I can bring in the list of GUIDs for all lists I was wondering if there was an "easy" way of querying this list  and where it matches the name of the list I need it then uses the appropriate GUID within the M query for that list.

 

Any ideas?

 

Cheers,

Andrew.

2 REPLIES 2
Super User
Super User

Re: Using a lookup in M in one query to set a parameter within another query

Hi @aexley

 

You could possibly do this by having one table that has a list of the GUID's

 

And then you have another table for your data.

 

Then Merge the tables into a new table in the Query Editor, and you can then do an Inner Join and match them on the GUID.

This will ensure that it only brings down the data where the rows are matched?

 

You could create a Parameter for your Source, or GUID.

And then when you are building up your SharePoint Source List, you can then use the Parameter GUID as part of your source. So that going forward all that you would need to do is to change the GUID Parameter and then it should point at the new location?


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog
aexley Member
Member

Re: Using a lookup in M in one query to set a parameter within another query

@GilbertQ Thanks. I had built the parameters out for the list GUIDs but there's a lot 🙂 So moving this through DEV, TEST and then PROD  environments means I will have to change the list parameters manually each time.

 

Just thought there may have been a way in M to automate this. Don't know of one myself but kind of wishful thinking someone may have some magic to do this.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)