repeated API calls until a null value is found

[NLblabla]

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"

[NLblabla]

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?

[v-jingzhang]

Hi @NLblabla 

You can do this in Excel. It is available in the Power Query Editor in Excel.

Jing

[v-jingzhang · Accepted solution]

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.