Paginated, Throttled, Parameterized API Data Sourcing

[Thilbetr]

Good afternoon,

Pooling our awesome community here for thoughts on approach. 

I am currently sourcing data from an API endpoint using Power Query on PowerBI Desktop. The API isn't the most ideal to work with and is fairly limited, so I have to take a recursive and exploratory approach. 

Currently, the way I have it set up:

Source 1 = API call, returning a list of valid numbers, which we'll call "Test Data IDs (TDIs)".

Source 2 = The TDIs then are each called individually (api.foo.bar/1/results, api.foo.bar/2/results, etc.) for all of their results. Easy so far, runs and pulls results for each TDI. The results for each TDI is also paginated, so the problem I'm having is running through a Custom Invoke against the TDI list, as we can only return the first 500 results for each TDI. There is no API Parameter to pre-query how many results are in each set. So I have to blindly query (api.foo.bar/1/results/page=1, api.foo.bar/2/results/page=2, etc) until the result is null. 

What is my best approach to cycle through each TDI set, and THEN as many pages as I can until a NULL() result is received from the API? The API is also throttled at 4 calls per second, which I handled with delayed function invokation at 1 second intervals.

[v-kkf-msft]

Hi @Thilbetr ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @Thilbetr ,

Maybe you could try using the $Skip ODATA expression to do pagination In Power Query.

For more information, please refer to:

https://www.reddit.com/r/PowerBI/comments/gwikkb/best_way_to_run_multiple_paginated_api_calls_in_a/ 

How To Do Pagination In Power Query 

https://community.powerbi.com/t5/Desktop/JSON-Joining-Records-in-Groups/td-p/327823 

Creating a loop for fetching paginated data from a REST API 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daryl-Lynch-Bzy]

Hi @Thilbetr - here is a video explaining the process: Pagination and DO/WHILE in Power BI / Power Query - YouTube

[Thilbetr]

Here is where I have roughly found myself so far. It doesn't appear to quite be taking... Below is the custom code for the custom invoked function. 

= (pst_id_param as number) =>
    let
    Source = List.Generate( ()=>
        [Result = try Function.InvokeAfter(()=> Json.Document(Web.Contents("https://us.api.knowbe4.com/v1/phishing/security_tests/"&Number.ToText(pst_id_param)&"/recipients?per_page=500?page="&Number.ToText(pageno), [Headers=[Authorization="Bearer ***secret code***"]])),#duration(0,0,0,1)) otherwise null, pageno=1],
        each [Result] <> null,
        each [Result = try Function.InvokeAfter(()=> Json.Document(Web.Contents("https://us.api.knowbe4.com/v1/phishing/security_tests/"&Number.ToText(pst_id_param)&"/recipients?page="&Number.ToText(pageno+1), [Headers=[Authorization="Bearer ***secret code****"]])),#duration(0,0,0,1)) otherwise null, pageno=[pageno]+1],
        each [Result]),
                #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"recipient_id", "pst_id", "user", "template", "scheduled_at", "delivered_at", "opened_at", "clicked_at", "replied_at", "attachment_opened_at", "macro_enabled_at", "data_entered_at", "qr_code_scanned", "reported_at", "bounced_at", "ip", "ip_location", "browser", "browser_version", "os"}, {"Column1.recipient_id", "Column1.pst_id", "Column1.user", "Column1.template", "Column1.scheduled_at", "Column1.delivered_at", "Column1.opened_at", "Column1.clicked_at", "Column1.replied_at", "Column1.attachment_opened_at", "Column1.macro_enabled_at", "Column1.data_entered_at", "Column1.qr_code_scanned", "Column1.reported_at", "Column1.bounced_at", "Column1.ip", "Column1.ip_location", "Column1.browser", "Column1.browser_version", "Column1.os"}),
        #"Expanded Column1.user" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.user", {"id", "provisioning_guid", "first_name", "last_name", "email"}, {"Column1.user.id", "Column1.user.provisioning_guid", "Column1.user.first_name", "Column1.user.last_name", "Column1.user.email"}),
        #"Expanded Column1.template" = Table.ExpandRecordColumn(#"Expanded Column1.user", "Column1.template", {"id", "name", "difficulty", "type"}, {"Column1.template.id", "Column1.template.name", "Column1.template.difficulty", "Column1.template.type"})
    in
        #"Expanded Column1.template"

[Daryl-Lynch-Bzy]

@Thilbetr - wow this looks complex.  The only thing that I a surpised by is the way that you have used Web.Contents.  It is better to specify the query path separately like the way the header is included.

Web.Contents(
   "https://us.api.knowbe4.com/v1/phishing/security_tests/"&Number.ToText(pst_id_param)&"/recipients?per_page=500?page="&Number.ToText(pageno),
    [Headers=[Authorization="Bearer ***secret code***"]]
)

this should look more like this:

Web.Contents(
    "https://us.api.knowbe4.com/v1/phishing/security_tests",
    [
       RelativePath = Number.ToText(pst_id_param) & "/recipients " 
       Query = [ per_page = 500 , page = Number.ToText(pageno)] ,
       Headers = [ Authorization="Bearer ***secret code***"]
    ]
)

[Daryl-Lynch-Bzy]

Hi @Thilbetr - you need to use a List.Generate function in the API function to check for Nulls on the final page.  Try the following post as an example:  How not to miss the last page when paging with Power BI and Power Query (thebiccountant.com)