Forum Discussion
repeated API calls until a null value is found
Hi all,
Hopefully you can help me. I want to get data from an API. I've got the API code working (code below, it is slightly anonimised) for one address, but I want it to repeat the API calls to create a larger table. It should do a call for all addresses in column A (amount differs from time to time), then merging it into one table. As the API only supports one address per call, it has to repeat the call until it walks into a null value. How can I arrange that part?
Many thanks in advance!
My current code:
let
AuthCode = "Bearer xxx",
Row = 1,
Address = Excel.CurrentWorkbook(){[Name="Address"]}[Content]{Row}[Column1],
Bron = Json.Document(Web.Contents("https://xxx.nl/api/v1.0/"&Address, [Headers=[Accept="application/json", Authorization=AuthCode]])),
#"Geconverteerd naar tabel" = Table.Transpose(Record.ToTable(Bron))
in
#"Geconverteerd naar tabel"
Hi NLblabla
You could try the custom function feature in Power Query. Keep all the amount values in column A. Convert your current query into a custom function with the amount value as a parameter in it. Then invoke the custom function to add a column. This new column has table values in its cells. You can then expand this column to show all the data from these APIs.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
3 Replies
Hi NLblabla
You could try the custom function feature in Power Query. Keep all the amount values in column A. Convert your current query into a custom function with the amount value as a parameter in it. Then invoke the custom function to add a column. This new column has table values in its cells. You can then expand this column to show all the data from these APIs.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.Thank you for your reply. Do I need Power BI Desktop to do this step-by-step way to create the custom function, or can I also do so in Excel?
