Storing and reusing a value as a variable/parameter for other queries

[shuhn1229]

Hi all,

Can someone let me know if I am approaching this problem from the right angle?

I have an excel sheet that contains a particular value in a specific cell that gets updated somewhat infrequently. I need to store this value as a variable and pass it to a few other independent queries that call an API, so that those APIs can fetch the right data contingent on this value. I want this all to happen automatically upon scheduled refresh so that when the result of that cell is first updated, it gets passed to the other queries so that they can pull the right data.

What I did was leverage the following code to create a one column list:

let
    Source = Excel.Workbook(Web.Contents("https://myurl.com/Token.xlsx")){[Name="myParameter"]},
    Data = Source[Data],
    Column1 = Data[Column1]
in
    Column1

Then I created a new parameter, selected "from query", & referenced the list generated by this query. The problem I am running into is that when I update the value in the spreadsheet, I have to manually select the new parameter in PBI desktop (edit parameters) to update that parameter, which will then flow to the other queries. Again, the idea is that when that value is updated, I store it as a variable and then patch it to all the other queries that would happen downstream of it. Am I thinking about this the right way?

Thanks

[watkinnc · Accepted solution]

You can just filter the query for the Excel data to just a list, and then instead of making a proper "parameter" from the GUI, just refer directly to the query as the list parameter. So if the query with your list from Excel is named "FilterList", then your other queries can use it as a parameter like:

let Source = SomeTable

ApplyList = Table.SelectRows(Source, each List.Contains(List.Buffer(FilterList), [ColumnNameBeingFiltered]))

--Nate

[shuhn1229]

Hi,

If I do this I get the following error: Formula.Firewall: Query ‘____’ (step ‘____’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Is there anyway around this?

Thx

[shuhn1229]

figured it out, ignore privacy.