Rest API - call the next page URL without knowing total rows or total pages

[Anonymous]

Hi Everyone, 

This is my first time working with a REST API in Power BI and I'm running into an issue with pagination...

I have an initial GET that calls out to a REST service which returns 100 results and a link for the next 100 results. This link is the same GET call but with an additional parameter page_token. So I need PowerBI to basically capture the results and then continue to loop through the rest of the results using this parameter and combine all the results together. 

I know there is quite a bit of documentation on pagination, like this Community post and this article by the BI Accountant, but all the solutions I can find require either knowing the total records or total pages or have the next page url listed explicitely in the inital record.

In my case, when the API is called, it returns this initial record. 

But you have to click into Pagination in order to grab the next URL:

Based on my research, I was able to piece together this List.Generate function, but I'm having trouble understanding how this would all come together -- how do I tie the next record from above into this List.Generate? 

Thanks in advance for your thoughts!

[bshresth]

Hi, Not sure if anyone out there can help. I used above example to get pagination working and data being pulled from Salesforce. 

Below is my code:

let
url = "https://asx--qa.sandbox.my.salesforce.com/services/oauth2/token",
headers = [#"Content-Type"="application/x-www-form-urlencoded"],
body="grant_type=password&client_id=" & sf_client_id & "&client_secret=" & sf_client_secret & "&username=" & sf_username & "&password=" & sf_password,
response = Web.Contents(url,[Content = Text.ToBinary(body),Headers = headers]),
result1 = Json.Document(response),
token = result1[access_token],

qry = "SELECT Id,Name,Active__c,BillingStreet,BillingCity,BillingState,BillingPostalCode,BillingCountry,Phone,Fax,Website,CreatedDate,LastModifiedDate FROM Account where Active__c = true and (RecordType.Name = 'Customer' or RecordType.Name = 'Prospect') and Listings_Prospect__c = true",

Source = List.Generate(
() => [ URL= sf_base_url,
Result=Json.Document(Web.Contents(URL,[RelativePath="/services/data/v60.0/query/", Query= [q=(qry)], Headers = [Authorization = "Bearer " & token]])) ],

each try [URL] <> null otherwise null <> null,

each [
URL = if ([Result][nextRecordsUrl] <> null) then sf_base_url else null, Result=Json.Document(Web.Contents(URL,[RelativePath=[Result][nextRecordsUrl], Query= [q=""], Headers = [Authorization = "Bearer " & token]]))]
),

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"URL", "Result"}, {"Column1.URL", "Column1.Result"}),
#"Expanded Column1.Result" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Result", {"totalSize", "done", "nextRecordsUrl", "records"}, {"Column1.Result.totalSize", "Column1.Result.done", "Column1.Result.nextRecordsUrl", "Column1.Result.records"}),
#"Expanded Column1.Result.records" = Table.ExpandListColumn(#"Expanded Column1.Result", "Column1.Result.records"),
#"Expanded Column1.Result.records1" = Table.ExpandRecordColumn(#"Expanded Column1.Result.records", "Column1.Result.records", {"attributes", "Id", "Name", "Active__c", "BillingStreet", "BillingCity", "BillingState", "BillingPostalCode", "BillingCountry", "Phone", "Fax", "Website", "CreatedDate", "LastModifiedDate"}, {"attributes", "Id", "Name", "Status1", "BillingStreet", "BillingCity", "BillingState", "BillingPostalCode", "BillingCountry", "Phone", "Fax", "Website", "CreatedDate", "LastModifiedDate"}),

#"Uppercased Text" = Table.TransformColumns(Table.TransformColumnTypes(#"Expanded Column1.Result.records1", {{"Status1", type text}}, "en-AU"),{{"Name", Text.Upper, type text}, {"Status1", Text.Upper, type text}}),
#"Added Custom" = Table.AddColumn(#"Uppercased Text", "Status", each if ([Status1] = "TRUE") then "Active" else "InActive"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Status1"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Status", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Column1.URL", "Column1.Result.totalSize", "Column1.Result.done", "Column1.Result.nextRecordsUrl", "attributes"})
in
#"Removed Columns1"

How ever when I go to Data source settings, I get error "Some data sources may not be listed because of hand-authored queries". I want the published report to get the fresh data from API in near real time. If I do not have this pagination, the error goes away and in power bi I have option to only refresh once daily. What is the option to have published report getting real time data via API?

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

The next page url is stored in Result[_pagination][next] so try this

let
    Source = List.Generate( () =>

        [ URL = "https://api2.frontapp.com/" , Result = Json.Document(Web.Contents(URL)) ],

        each [URL] <> null,

        each [ URL = [Result][_pagination][next] , Result = Json.Document(Web.Contents([URL])) ]

    )
in
    Source

This code will end assuming that [_pagination][next] is null when there are no more records to retrieve.

Regards

Phil

[Anonymous]

Thank you Philip! That [_pagination][next] was the piece I was missing!