Introduction:
With some REST APIs, you can get records count with $count. we usually do that in one step and divide it by the number of records returned per call, and then use List. Numbers (0, count step/number, number). That make a list that increments by number 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.
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
The @ symbol means that you can refer to a variable that is part of the code which defines the variable. While this is usually used to recursively call functions you could also put items inside itself, due to records being lazyevaluated. E.g.
let A = {@A} in A
which is equivalent in Java/c# to:
Object[] A = new Object[1];
A[0] = A;
What you need to do is, put any variable you want to modify in the parameter list of the function.
Thank You,
Lakshmi Boga
Helical IT Solutions Pvt Ltd
Best Open Source Business Intelligence Software Helical Insight is Here
