This PowerQuery/M code automatically grabs multiple pages of dreamfactory JSON recursively. The column will still need to be expanded, etc, as usual.
Improvements welcome, I'm by no means a powerquery expert, but I wanted to get dreamfactory working with PowerBI. While this is tailored to dreamfactory, it should work with any paged api.
PowerBI is a data visualization application developed by Microsoft: https://powerbi.microsoft.com/en-us/ . The PowerQuery language is also used in Excel.
let getDFUrl = (url as text, optional previousResult) =>
Res = Json.Document(Web.Contents(url)),
combination = if (previousResult <> null) then
Table.FromList(Res[resource], Splitter.SplitByNothing(), null, null, ExtraValues.Error)
Table.FromList(Res[resource], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
if (offset <> null) then
if (offset > 0) then
@getDFUrl(url & "&offset=" & Number.ToText(offset), combination)
This query seems to have issues in Excel at the moment, but PowerBI Desktop is working fine. Note that PowerBI Webservice can't use recursive functions to build URLs at the moment (April 2017), so you'll have to "unroll the loop" and hardcode all your URLs if you need to connect to the API via the PowerBI Web Service Scheduled Update.