Forum Discussion
how to fully load data from limited HTTP API?
what I need is something similar to this topic:
https://community.powerbi.com/t5/Desktop/how-to-create-a-query-that-paginates/td-p/20047/page/2
but somehow I still could not find a solution for me.
my server API returns this:
offset=actual starting value, if not set it returns 0, to start at row 0;
limit=maximum is 100;
next_offset=show the start of the next offset. if I start offset=300, limit=100, but there are only 7 more rows to load, next_offset will be 307. if there are thousands more, next_offset will always be offset+limit, in this example 300+100=400.
from_cache=TRUE if there is more data, FALSE if no more data to show. but I think I can use only the next_offset value in this loading procedure.
See if you can adapt this for your scenario:
https://www.linkedin.com/pulse/loading-data-paged-related-from-ms-graph-api-power-bi-rob-reilly
Otherwise, you will have to create some kind of logic within your query to use the offset+limit only if the count of results is a multiple of 100. That way, if the result count is 307, it will know that there are no more results. Additionally, if there are exactly 300 (or other multiple of 100) results, then offset+limit wont work, so you will have to incorporate some error handling.
hi dkay84.
I read the link twice, tested both scripts in it, but they simply does not work.
A simple copy+paste shows error on the second line: missing Eof "GetUserDetail = (Path)=>".
what does that means?
I am new to M language and I cannot find the error on the script.
please, I still need help.
I tried to modify the script from another post, but then Power BI freezes for a second and then returns and error.error: An error occured when deserializing the results. try to repeat the operation.
below is the full script that I would like someone to review and correct.in bold, commands that I don´t understand, but I am using anyway because I just adapted from another post, and I think this is the way to get my solution.
let
Source = Json.Document(Web.Contents("https://url.com/token?limit=100&offset=0&date_range=201001010000:201912310000")),
Pagination = List.Skip(List.Generate( () => [offsett = "0"], // Start Value
each Source[offset] <> Source[next_offset], // Condition under which the next execution will happen
each [ WebCall = "https://url.com/token?limit=100&offset="&[offsett]&"date_range=201001010000:201912310000", // retrieve results per call
offsett = if WebCall[next_offset] >= WebCall[offset] then WebCall[next_offset] else WebCall[next_offset] ],// determine the offset for the next execution
each [Value]),1) // Select just the Record of the last step from your query
in
PaginationI tried to wrap the webcall under Json.Document(Web.Contents()), but this also doesn´t work.
for information, the first line (Source) returns this, which is ok.i need to use and compare the values from offset and next_offset.
the from_cache value is not good for this script, because it returns false or true even if I still have more data to read.
Anonymous wrote:I tried to modify the script from another post, but then Power BI freezes for a second and then returns and error.
error: An error occured when deserializing the results. try to repeat the operation.
Do you know what the format is of the results (XML, JSON, etc)? Is your token something that you can change so that you can share it and the API url with us? Or can you point to another API that is public that has a similar structure of returned results?