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

[Anonymous]

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.

[GilbertQ]

Hi @Anonymous

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?

[Anonymous]

@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.