Here’s some javascript that generates the unrolled loop. Note that for long queries (10s of thousands of records) powerbi may hang or bluescreen your computer. Getting millions of DF rows may take a very long time. This code will work in the web service, but I speculate it is non-optimal. Or the PowerBI web api grabber is terribly inefficient. Where possible, use the recursive code in the original post, but if you need refreshes in the web service, this is the best way I’ve found.
To use, open up Dev Tools in Chrome (F12). Open the console and paste this code in. Then change the count to the number of pages you want (Dreamfactory defaults to 1000 records per page). Change the URL to the url to get the data, with filters, etc. Nothing will appear to happen, but the result will be on your clipboard.
var count = 10; //number of pages
var url = "http://YOUR_DF_URL/?with_filters=and_api_key&etc=etc";
var output = "let \n";
for (var i=0; i<count; i++) {
output += `
source${i} = Json.Document(Web.Contents("${url}&offset=${i * 1000}")),
resource${i} = source${i}[resource],
table${i} = Table.FromList(resource0, Splitter.SplitByNothing(), null, null, ExtraValues.Error), \n
`;
}
output += " FinalSource = Table.Combine({\ntable0\n";
for (var i = 1; i<count; i++) {
output += ",table" + i + "\n";
}
output += `})
in FinalSource
`;
copy(output) // paste this into the advanced query editor (right click your query name to access the advanced editor).
This generates code like this:
let
source0 = Json.Document(Web.Contents("https://vtransapi.aot.state.vt.us/api/v2/crash/_table/pub.PQT_AccidentHeader?api_key=e17ab0a9946ee4e2921a8c80b4949ba81ef897cb03e84a032edd3ecd2925bbb5&fields=REPORTNUMBER,ContribCircumstances1,Light,RoadType,RoadAlign,RoadDesign&limit=1000&offset=0")),
resource0 = source0[resource],
table0 = Table.FromList(resource0, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
source1 = Json.Document(Web.Contents("https://vtransapi.aot.state.vt.us/api/v2/crash/_table/pub.PQT_AccidentHeader?api_key=e17ab0a9946ee4e2921a8c80b4949ba81ef897cb03e84a032edd3ecd2925bbb5&fields=REPORTNUMBER,ContribCircumstances1,Light,RoadType,RoadAlign,RoadDesign&limit=1000&offset=1000")),
resource1 = source1[resource],
table1 = Table.FromList(resource1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
source2 = Json.Document(Web.Contents("https://vtransapi.aot.state.vt.us/api/v2/crash/_table/pub.PQT_AccidentHeader?api_key=e17ab0a9946ee4e2921a8c80b4949ba81ef897cb03e84a032edd3ecd2925bbb5&fields=REPORTNUMBER,ContribCircumstances1,Light,RoadType,RoadAlign,RoadDesign&limit=1000&offset=2000")),
resource2 = source2[resource],
table2 = Table.FromList(resource2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
source3 = Json.Document(Web.Contents("https://vtransapi.aot.state.vt.us/api/v2/crash/_table/pub.PQT_AccidentHeader?api_key=e17ab0a9946ee4e2921a8c80b4949ba81ef897cb03e84a032edd3ecd2925bbb5&fields=REPORTNUMBER,ContribCircumstances1,Light,RoadType,RoadAlign,RoadDesign&limit=1000&offset=3000")),
resource3 = source3[resource],
table3 = Table.FromList(resource3, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
source4 = Json.Document(Web.Contents("https://vtransapi.aot.state.vt.us/api/v2/crash/_table/pub.PQT_AccidentHeader?api_key=e17ab0a9946ee4e2921a8c80b4949ba81ef897cb03e84a032edd3ecd2925bbb5&fields=REPORTNUMBER,ContribCircumstances1,Light,RoadType,RoadAlign,RoadDesign&limit=1000&offset=4000")),
resource4 = source4[resource],
table4 = Table.FromList(resource4, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
[...]
FinalSource = Table.Combine({table0,
table1,
table2,
table3,
table4,
[...]
})
in
FinalSource