How to switch sources within sharepoint folder

[pkoetzing]

I'm using an Excel file with various tables as input source to my queries. As long as this file is stored on my local drive, I can simply switch to another file using "data source settings" - "change source". But when the file is located within a sharepoint folder I don't have that option. Instead I would have to change the path in every single query using the advanced editor. Is there any way to use a kind of "global parameter" in PowerBI so that this change needs to be done in one place only?

[jaryszek]

Hi Guys,

new idea for it:
https://community.fabric.microsoft.com/t5/Fabric-Ideas/Skip-Credential-Validation-for-Queries-Exclud...

Please vote!

It means that you can exclude query to be refreshed not only in power bi desktop - also within power bi service without failing because of data source credentials. 

Best,
Jacek

[pkoetzing · Accepted solution]

Just figured it out myself: Goto "Query Editor". On "Manage Parameters" select "New Parameter" and enter a name, a list of values and a current value. Then in "Advanced Editor" simply refer to that parameter, e.g.

    File = Source{[Name=MyFileParameter,#"Folder Path"=MyPathParameter]}[Content]

[anacerco]

Hello!

I have same issue and I am not exactly sure how you define a list of values for a parameter, could you please explain?

[pkoetzing]

Hi,

Actually I wasn't using a list of values for one parameter, but different parameters for site_url, folder_name and file_name. But in the Power Query Editor you can go to 'Manage Paremeters' and under 'Suggested Values' select 'List of values' and then type your list in.

See an example here: https://powerbi.microsoft.com/de-de/blog/deep-dive-into-query-parameters-and-power-bi-templates/

[JoeRobert06]

if you can provide the m code for this example, that would be awesome.

Thanks for the share!

[JoeRobert06]

I figured it out, see below screenshot as reference. You can create parameters for each argument shown below in yellow and do not need to place (") or (&) symbols. In my case, all I needed was a parameter to toggle the filepath and left the other arguments alone.