API call in custom column

[SanderVeeken]

Hi all,

I'm working with an API that first gives me a basic list of products with ID, and then I can use this product ID to make further API calls to get more information about each product. With something like Python, this is very straightforward. This is the first time I've tried to do this in PowerQuery and I figured it would be as simple as adding a custom column and putting the API call in there. Simplest version:

#"GETextrainfo"=Table.AddColumn(#"previous step", "extrainfo", each Json.Document(Web.Contents(#"API_PATH" & Text.From([id]) & "?Endpoint-Access-Token=" & #"Endpoint-Access-Token")))

However, no matter how I phrase this function, I get an error:

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

No other queries are referenced, the only step that is referenced is the previous one which is normal. So it must be a problem making this API call in a column? How then would I do it? I'm not a star in M Language, but it seems so simple and it's driving me crazy that it doesn't work.

[lbendlin]

Not simple at all, you picked one of the most complex topics in Power Query.

https://learn.microsoft.com/en-us/power-query/data-privacy-firewall

https://blog.crossjoin.co.uk/2019/06/18/detailed-article-on-the-power-query-formula-firewall-and-dat...