I need to load all data from the API, but the server returns a maximum of 100 rows.at the link below, if I change the limit=100 to a bigger number, it returns with 100.to get more data, I need to change offset=0 to another part, for instance offset=100, offset=200 and so on. https://<testserver>.com/<token>?limit=100&offset=0&date_range=201701010000:201712310000 I did manage to use parameters under WEB load, advanced options, but it loads only one set of 100 rows, and I need to import ALL rows from this link.the code below works, but does not returns full table, and I need full table. =Json.Document(Web.Contents("https://<testserver>.com/<token>?limit=100&offset=" & offsettt & "&date_range=201001010000:201912310000"))in this code, offsettt is the list I made with possible values for offset, but it returns only 100 rows at max.today, maximum possible offset is around 3000, but everyday this database keeps growing. how do I loop this loading process, to get all possible rows in one query?

For anyone who comes across this in future, the LinkedIn link above contains a sample script to recursively access Microsoft Graph API. However cut and pasting the code will not work as there are a number of errors - which are described in the comments at the bottom. The corrected code can be found here: // ############## BEGIN QUERY ############################ let // Define a Function Called GetUserDetail that takes a single parameter Path // that is the MS Graph API Endpoint GetUserDetail = (Path)=> let // call out to the web endpoint and convert the result to a JSON // Document object which is saved in the Source variable Source = Json.Document(Web.Contents(Path)), // Assign the resultant List object to NextList variable NextList= @Source[value], // recursively call GetUSerDetail function using the value of the // @odata.nextLink as the path // Since @odata.nextLink is not guaranteed to always exist in the case where // the result is less than 100 we need to catch this and deal with it // using a try otherwise basically means if you error out because no // @odata.nextLink exisit then just return the resulting list of values you did get result = try @NextList & @GetUserDetail(Source[#"@odata.nextLink"]) otherwise @NextList in // Return the result variable as the function return result, // Use the function GetUserDetail with the initial path to the object. // The function will run recursively until it gets all the record and then // loads the full result to UserDetail Variable UserDetail = GetUserDetail("https://graph.microsoft.com/beta/users?$expand=manager")in UserDetail// ##################### END QUERY #######################

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/2but 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. letSource = Json.Document(Web.Contents("https://url.com/token?limit=100&offset=0&date_range=201001010000:201912310000")),Pagination = List.Skip(List.Generate( () => [offsett = "0"], // Start Valueeach Source[offset] <> Source[next_offset], // Condition under which the next execution will happeneach [ WebCall = "https://url.com/token?limit=100&offset="&[offsett]&"date_range=201001010000:201912310000", // retrieve results per calloffsett = if WebCall[next_offset] >= WebCall[offset] then WebCall[next_offset] else WebCall[next_offset] ],// determine the offset for the next executioneach [Value]),1) // Select just the Record of the last step from your queryinPagination I 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.

how to fully load data from limited HTTP API?

12 Replies

Anonymous
Not applicable
9 years ago
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.
- dkay84_PowerBI
  Microsoft Employee
  9 years ago
  
  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.
  - Anonymous
    Not applicable
    9 years ago
    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.

Forum Discussion

how to fully load data from limited HTTP API?

12 Replies

Related content

Copilot, limitations?

Cannot load model due to reaching capacity limits

Data limit Issues

Reaching capacity limit - Unable to load model due to reaching limits

Get DP-700 for FREE — Limited Offer