I am trying to use List.Generate to loop through a number of API calls. I am running, though, into a problem. Here is my code:

Steps Taken

Step #1 - Create a list of stock symbols

Step #2 - Get a Count of That List

Step #3 -- Use List.Generate to loop through the Stock Symbols:

Here is the code I used to generate the list:

= List.Generate(
    () => [index = 0, ticker = Source{index} ],
    each [index] < SourceCount,
    each [index = [index]   1, ticker = Source{index} ]
)

Step #4 -- Create a Function in Another Query

In addition, I have another query that is a function which queries an API call. Here is the code for that function:

= (tickerSymbol) => 

let
    Source = Json.Document(Web.Contents("https://[WORKSPACE].iex.cloud/v1/data/core/income/" & tickerSymbol & "/annual?range=10y&token=[API_KEY]"))
in
    Source

This function works. I have tested it out.

Step #5 -- Put it All Together

Now, I want to use that function within List.Generate (from my first query). To do this, I rewrite the List.Generate function I mentioned above as follows:

= List.Generate(
    () => [index = 0, ticker = Source{index}, stockData = getStockData(ticker) ],
    each [index] < SourceCount,
    each [index = [index]   1, ticker = Source{index}, stockData = getStockData([ticker]) ]
)

The Problem

It is at this point that I run into a proble. When I try to modify List.Generate as mentioned above, I get the following error message:

Formula.Firewall: Query 'Query1' (step 'GenerateList') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Now, I am basically following this tutorial step by step: https://youtu.be/a_RJzoj1cnM

In the tutorial it works, but in my variation of the tutorial it does not. I should note, I am new to Power Query and am not sure what the warning message means.

Either way, any idea what I am doing wrong and how to fix it?

Thanks.

CodePudding user response：

That seems to be very convoluted

Cant you just pull in the Symbols as source, convert list to table, then add a new custom column with formula

= getStockData([ColumnNameOfStockSymbolsHere])