Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NLblabla
Frequent Visitor

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"

 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
NLblabla
Frequent Visitor

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?

Hi @NLblabla 

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

 

Jing

v-jingzhang
Community Support
Community Support

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.