Forum Discussion
REST api request and loop by offset until no further records found
Hi,
i am facing the challange of requesting data from a REST service that limits the records by 250 without sharing how many records should retrieved at all. So i am not able to calculate in advance how often i've to call the api with the offset of 250.
I am looking for a solution to implement a List.Generate() until the last record has been read. I was wonder if i could loop until the record counts becomes 0.
I am pretty new in power bi, so don't judge me for my ugly code snippets 😉 But this is what i am currenctly expericen with:
my_list= List.Generate(
() => [offset = 250, offsetX = {0}],
each [offset] <> List.Count([offsetX]) ,
each [offsetX = List.Transform({},each Json.Document(Web.Contents("https://serverurl.com",
[RelativePath="/index.php?/api/api_url/" & "12" & " &limit=250&offset=" & ???,
Headers=[#"Content-Type"="application/json"]])))
]
),
maybe someone has already solved this challange and can share some snippets i sould use as well.
//joerg
First, check the request metadata to see if it tells you how many records there are.
E.g.
let
webData = Web.Contents("https://..."),
webMetadata = Value.Metadata(webData)
in
webMetadata
For doing a while loop in power you can instead use recursive functions like:
let my_func = (startIndex) => let webRequest = Web.Contents(...), ...., results = ..., numRecords = ...., if numRecords = page_size then results & my_func(startIndex + page_size) else results in my_func(0)Note the @ used for recursion
13 Replies
First, check the request metadata to see if it tells you how many records there are.
E.g.
let
webData = Web.Contents("https://..."),
webMetadata = Value.Metadata(webData)
in
webMetadata
For doing a while loop in power you can instead use recursive functions like:
let my_func = (startIndex) => let webRequest = Web.Contents(...), ...., results = ..., numRecords = ...., if numRecords = page_size then results & my_func(startIndex + page_size) else results in my_func(0)Note the @ used for recursion
Thanks artemus for the fast response!
As i am a novice in power bi i like to learn also more.
in you snippet you used:
...
results & @my_func(startIndex + page_size)
it's the firsttime that i saw the "&" ... what is the explanation for this syntax?
Would be great to get your support!
//joerg
The & operator does the following:
- Union 2 tables (as in the example above): #table(type table [A = number, B = text], {{1, "A"}, {2, "B"}}) & #table(type table [B = text, C = logical], {{"C", true}, {"D", false}}) = #table(type table [A = number, B = text, C = logical], {{1, "A", null}, {2, "B", null}}, {{null, "C", true}, {null, "D", false}})
- Combine 2 lists: {1, 2, 3} & {4, 5, 6} = {1, 2, 3, 4, 5, 6}
- Combine 2 records (a record is a property bag or table row): [A = "Hi", B = 2] & [B = 5, C = #date(2020, 06, 27)] = [A = "Hi", B = 5, C = #date(2020, 06, 27)
- Combine date with time: #date(2020, 06, 27) & #time(13, 45, 15) = #datetime(2020, 06, 27, 13, 45, 15)
- Concatnate text: "Hello" & "Goodbye" = "HelloGoodbye"
Thanks artemus for this, exactly what I was after.
Only one thing for the next reader, you forgot an "in" after your numRecords =
Query should be:
let my_func = (startIndex) => let webRequest = Web.Contents(...), ...., results = ..., numRecords = ...., in if numRecords = page_size then results & @my_func(startIndex + page_size) else results in my_func(0)
With some REST APIs, you can get a records count with $count. I usually do that in one step and divide it by the number of records returned per call (250 in your case), and then use List.Numbers(0, countstep/250, 250). That make a list that increments by 250 as many times as needed to get all the records. You then convert that to table and the number column to text, and then concatenate that with the web call in a custom column. From there, you can expand that column to combine the data from all the calls.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Thanks for your contribution Mahoneypat,
but my rest service doesn't provide any information about the total amount of records or how many pages have to be visited.
I honestly have the challenge to iterate unitl the amount of records per page are zro.
//joerg
Now, i am close to a solution by compiling this invoke function, except on thing ... please HELP
let
Source = (runID as number,offset as number, counter as number) => letcounter = counter +1,
webData = Json.Document(Web.Contents("https://serverurl.com",
[RelativePath="/index.php?/restapiFunc/" & Number.ToText(runID) & "&offset=" & Number.ToText(offset),
Headers=[#"Content-Type"="application/json"]])),
resultList = if List.Count(webData) = 250 then
webData & @all_results_by_id(runID, offset + counter*250, counter)
else
webData
in
resultListin
Source----------------------------------------
If the rest responses 2*250 records and the last one for instance 46 records, the overall result of this function keeps 500 and not 546. Means: The last set of less than 250 is missing....
please help before it becomes to frustrating!
