Programmatically point Power Query to a different SharePoint list

[asecgeek]

I have an Excel workbook that pulls data from several SharePoint lists on a Microsoft 365 tenant for ABC Company via Power Query.
I have the exact same SharePoint lists exist at XYZ Company's tenant.
I would like to have a button that programmatically switches the Power Query source from one tenant to the other.
I can manually change the Source and Navigation values by editing the settings in Power Query's Source and Navigation formulae, but I can't expect management to do that.
So, I would like to have a button that changes it. Example (with bogus values):

Source = = SharePoint.Tables("https://abc.sharepoint.com/sales/lists/", [Implementation=null, ApiVersion=15])
Navigation = = Source{[Id="67bdt8ed-7447-46w2-w2r2-gh247m74m867"]}[Items]

Source = = SharePoint.Tables("https://xyz.sharepoint.com/sales/crm/", [Implementation=null, ApiVersion=15])
Navigation = = Source{[Id="r9pse8wq-784m-58k4-iyr4-xd428wd55pq2"]}[Items]

Is it possible to modify these values with the click of a button?

 

[RoryA]

I'd use a parameter query. See for example Ken's blog here: Creating Dynamic Parameters in Power Query - The Excelguru Blog

 

[asecgeek]

I looked into the link and I think it's pointing me in the right direction. Will report back with results.

 

[GraH]

If you don't want people to go into the PQ UI, you can creatie a dropdown in Excel for the managers. Then PQ uses this single field table ( you name it SPvar or something in the name manager) to create a 'parameter'. In the source you refer to this query name where you have the SP address.

 

[asecgeek]

Yes, GraH, this is exactly what I did. Works great. Thanks.