Iterate Through API Calls with Content

[Anonymous]

I have an API that has a limitation on the number of identifiers you can send in at once. Because of this I need to be able to call this API and iterate through it dynamically, so I can send as many identifiers at once and it will always pull all of them regardless of size.

Right now I have each payload (broken up into the max 500 identifiers) in Binary format in a table like the below (it was text but then I would have to convert to Binary anyway before sending):

payload

Binary

Binary

Binary

What I can't figure out is how to iterate over my API call which is in the below format as of now: 

= try
let
webdata = Json.Document(Web.Contents(apiUrl, [Headers=headers,Content=[payload]]))
in
webdata

so that is references the payload table and iterates over each row and gives the result in a list. I attempted List.Generate and Table.AddColumn and I would expect the output to be like something below:

Results

Value

Value

Value

Some of my attempts:

= Table.AddColumn(payload, "Results", each Json.Document(Web.Contents(apiUrl, [Headers=headers,Content=[payload]])))

Resulted in this error:

Expression.Error: We cannot convert a value of type Table to type Binary.

(This time sending in the table as text and then trying to convert to binary in the function)

= Table.AddColumn(payload, "Results", each Json.Document(Web.Contents(apiUrl, [Headers=headers,Content=Text.ToBinary[payload]])))

Resulted in this error:

We cannot apply field access to the type Function.

Any help is appreciated

[ImkeF]

Hi @Anonymous ,
any "detect-data-type"-action in Power Query will currently result in a hardcoded definition of column types, unfortunately.
Would be great if you could mark my first post as solution, thanks.

[Anonymous]

Thank you so much that worked! As a followup - when the data is retrieved it gets back different columns each time (depending on the identifiers sent in) and then to be able to visualize it I have to use the "Detect data Type" button as the final step. The problem I am seeing with this though is that since the "Detect Data Type" button actually pulls in the columns names when I set it up, if I send in different identifiers it results in an error. And I do have my Options / Settings set to "Always detect column types and headers for unstructured sources" (although it doesnt seem to be doing anything). Is there a custom function or way to call that button dynamically, as having to delete that final step and hitting "Detect Data Type" again each time isn't ideal. (I can also submit this as a separate question)

[ImkeF · Accepted solution]

Hi @Anonymous ,
Table.AddColumns should work. Very strange that your first attempt returned that error-message (if the column "payload") really has binary content in it.
However, there is a syntax-error in your second attempt, maybe fixing that will make it work:
= Table.AddColumn(payload, "Results", each Json.Document(Web.Contents(apiUrl, [Headers=headers,Content=Text.ToBinary([payload])])))

[Anonymous]

Thank you so much that worked! As a followup - when the data is retrieved it gets back different columns each time (depending on the identifiers sent in) and then to be able to visualize it I have to use the "Detect data Type" button as the final step. The problem I am seeing with this though is that since the "Detect Data Type" button actually pulls in the columns names when I set it up, if I send in different identifiers it results in an error. And I do have my Options / Settings set to "Always detect column types and headers for unstructured sources" (although it doesnt seem to be doing anything). Is there a custom function or way to call that button dynamically, as having to delete that final step and hitting "Detect Data Type" again each time isn't ideal. (I can also submit this as a separate question)