Forum Discussion
REST api request and loop by offset until no further records found
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
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 for your contribution, artemus!
I've still not reached my target but struggle brave forward 😉
My current development based on your input is:
-----------------------------------------
let
counter = 0,
my_func = (offset as number) =>
let
counter = counter +1,
webData = Json.Document(Web.Contents("https://servername.com",
[RelativePath="/index.php?/api/url/2476&offset=" & Number.ToText(offset),
Headers=[#"Content-Type"="application/json"]])),offset = List.Count(webData),
resultList = if offset = 250 then
resultList & my_func(offset + counter*250)
else
resultList
in
resultListin
my_func(0)--------------------------------------------------------------
By running thiw query i got following error:
An error occurred in the ‘’ query. Expression.Error: The name 'resultList' wasn't recognized. Make sure it's spelled correctly
by declaration of this variable like
resultList = {} // at the begin of the query
i go follwoing error:
An error occurred in the ‘’ query. Expression.Error: A cyclic reference was encountered during evaluation
Would be great to get some good example how the recursive loop works or even better... help to fix my code.
Thanks in advanced!
//joerg
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)
