Web API - Pagination of results restricted to 100 by API

[jchipps]

Hi, I have seen multiple posts in relation to pagination of results for a Web API call, however it doesn't mater how many I try I can't get mine to work. 

The API I am calling is limited to 100 results every time but I need to compbine multiple results in to one data set so that I can report on the results.

My query is:

let
Source = Json.Document(Web.Contents("[URL]")),
result = Source[result],
records = result[records],
#"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "start", "answer", "end", "src", "dst", "from_number", "to_number", "from_name", "to_name", "billsec", "disposition", "lastapp", "userfield", "faxdata", "lastdata", "cost", "organization", "channel", "dstchannel", "app", "callclass", "dest_type", "duration", "dcontext", "pbxid", "type", "timezone"}, {"id", "start", "answer", "end", "src", "dst", "from_number", "to_number", "from_name", "to_name", "billsec", "disposition", "lastapp", "userfield", "faxdata", "lastdata", "cost", "organization", "channel", "dstchannel", "app", "callclass", "dest_type", "duration", "dcontext", "pbxid", "type", "timezone"})
in
#"Expanded Column1"

This gives me the first 100 results.

If i then run a seperate Web API for the following it returns a further 18 results (there are only 118 results at this time)

let
Source = Json.Document(Web.Contents("[URL]/?&start=100")),
result = Source[result],
records = result[records],
#"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "start", "answer", "end", "src", "dst", "from_number", "to_number", "from_name", "to_name", "billsec", "disposition", "lastapp", "userfield", "faxdata", "lastdata", "cost", "organization", "channel", "dstchannel", "app", "callclass", "dest_type", "duration", "dcontext", "pbxid", "type", "timezone"}, {"id", "start", "answer", "end", "src", "dst", "from_number", "to_number", "from_name", "to_name", "billsec", "disposition", "lastapp", "userfield", "faxdata", "lastdata", "cost", "organization", "channel", "dstchannel", "app", "callclass", "dest_type", "duration", "dcontext", "pbxid", "type", "timezone"})
in
#"Expanded Column1"

I need to be able to run this as a single API call but return all of the results, the number of results will increase so need to be able to add a way to do multiple calls based on data being returned, i.e if i run the API and there are actually 245 results it will call the API a 3rd time with /?&start=200 appended to the initial web API

Hope this makes sense.

[blopez11]

You esssentially need a query that loops through the pages and combines the results into a single list, where you then convert to a table then apply any other steps that need to be done

With the help of the reference below, we have been able to do this successfully (note that I used List.Combine, instead of the List.Union in the reference)

Good luck

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

[jchipps]

Thanks for this suggestion. I have already come across that post, however being new to the coding in the back end of PowerBI I am still unable to get this to work.

[blopez11]

Without knowing your API, it will be difficult to help you

Does the API call you are using return you the total # of records, or is their another API call that returns the total number of records?

Looking at the referred to reference:

// ...This part figures out the total number of records in the data set (you need to find a way your api returns this)

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),

// ...This part just figures out the number of queries you will issue based on total records and records returned in a page request
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),

// ...This part just normalizes the indices
PageIndices = { 0 .. PageCount - 1 },

// ...This part is the one that queries the pages, iterating through PageIndices
Pages = List.Transform(PageIndices, each GetPage(_)),

Hope this helps further

[jchipps]

Running https://[PBX]/api/pbx/callhistory/?&count=true

returns the following in a web page

{"type":"result","result":{"total":118,"records":[]}}

This is the correct number of records but can't pass this in to the query.

Looks like the below in BI

Thanks 

[Anonymous]

@jchipps convert that total to table 

[jchipps]

OK, so slightly changed the query to the below and it works perfectly in PowerBI Desktop, however when I publish this to PowerBI online the datasource does not refresh?

let
ufnQuery = (n) =>
let
jsonDoc= Json.Document(
Web.Contents(
"[replace this with web address]/?&start=" & Number.ToText(n))),
result = jsonDoc[result],
records = result[records],
tmpTbl = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
tmpTbl,

fnRecursive = (tbl, n) =>
if Table.RowCount(ufnQuery(n)) > 0 then @fnRecursive(Table.Combine({tbl, ufnQuery(n)}), n+100) else tbl,

tbl = ufnQuery(0),
final = fnRecursive(tbl ,100),
#"Expanded Column1" = Table.ExpandRecordColumn(final, "Column1", {"id", "start", "answer", "end", "src", "dst", "from_number", "to_number", "from_name", "to_name", "billsec", "disposition", "lastapp", "userfield", "faxdata", "lastdata", "cost", "organization", "channel", "dstchannel", "app", "callclass", "dest_type", "duration", "dcontext", "pbxid", "type", "timezone"}, {"id", "start", "answer", "end", "src", "dst", "from_number", "to_number", "from_name", "to_name", "billsec", "disposition", "lastapp", "userfield", "faxdata", "lastdata", "cost", "organization", "channel", "dstchannel", "app", "callclass", "dest_type", "duration", "dcontext", "pbxid", "type", "timezone"})
in
#"Expanded Column1"

[Anonymous]

@jchipps  diid uget any error when refreshing?

[jchipps]

@Anonymous I don't get an error when I refresh the report it just doesn't run the API again.  If I go in to Scheduled Refresh for the DATASET it shows the following

[Anonymous]

try this 

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

[jchipps]

@Anonymous That has got me one step closer, I can now manually refresh the data in PowerBI online, but want it doesnt do is refresh the dataset if I refresh the report?  I need to be able to publish to a website that refreshes the report which should refresh the data on the backend to get up to date information?

Thanks

[Anonymous]

refreshing the report wont refresh the dataset unless its direct query. U need to schedule the report or use microsoft flow and power apps to have a refresh button added in report to refresh the dataset

[jchipps]

@Anonymous Is there no way to make the Web API call a Direct Query, like we can for the SQL connector that we use on another report?

[Anonymous]

I have not tried but by using custom connectors, looks like u can achieve this..follow link below.

https://www.progress.com/tutorials/odbc/directquery-your-custom-rest-api-in-power-bi