Simplifying ADLS queries

[ljag]

Hi all,

I maintain a pretty large report that currently has a bunch of queries to our Azure Data Lake -- 14 of them as of now.

We have both dev and prod spaces for the Azure space and keep dev and prod versions of the PBI report as well. We test new data and visuals in the dev report on dev data, then switch to prod data and publish as a prod report.

Dev and prod spaces are denoted by a "d" or "p" in the ADLS web address in the PQE source and navigation steps (for example adls-d.dfs.core.windows.net/folder or adls-p.dfs.core.windows.net/folder). Currenly I have to just go in to each query and manually change the "d" or "p".

Basically my question is if there is some kind of helper query or shortcut I can use to change it only once and have it flow through to the rest of the queries so I don't have to go into these steps for all 14 of them and change a letter.

Thanks!

[Anonymous · Accepted solution]

Hi @ljag ,
Based on your description, you want to dynamically change your data source path to simplify the query process. You can use parameters to accomplish this process. Create a parameter in the power query and apply it to the fetch path. You can see how to do this in the following documentation
Solved: How to bulk change the source location for queries... - Microsoft Fabric Community
How to Parameterize Data Sources in Power BI | phData
Change the Source of Power BI Datasets Dynamically Using Power Query Parameters - RADACAD

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ljag]

this is great, thank you!

for future visitors, here is what to do:

- go to power query editor "Manager Parameters", then "New Parameter"

- name the parameter (I called mine adls_path), set type to Text

- create a list of values, one with the dev address and one with the prod address

- for every ADLS query, under Source and Navigation steps, replace the hardcoded address with adls_path

- to switch between dev and prod, go to the adls_path parameter and toggle the value to the one you want