Query Data in Bulk with Pagination

Developers can use "offset" and "limit" parameters to pull large datasets from the Wood Mackenzie Data API with multiple queries.
To help you get started, here is an example of a query in the M language that could be used in Power BI or Excel:

let GetData = (URL as text, genApiKey as text) => 
let pageSize = 5000,
sendRequest = (page) =>
let Response = Web.Contents(URL & "?limit=" & Number.ToText(pageSize) & "&format=json&offset=" & Number.ToText(page * pageSize), [Headers=[#"Gen-Api-Key"= genApiKey]]),
Data = Json.Document(Response)[data]
Loop = (page as number, AccumData as list) =>
Data = Function.InvokeAfter(()=>sendRequest(page), #duration(0,0,0,2)),
Result =
if List.Count(Data) < pageSize
or page >= 200
then Table.FromRecords(List.Combine({AccumData, Data}))
else @Loop(page + 1, List.Combine({AccumData, Data}))
Loop(0, {})

This example has a few features that we recommend for best performance:

  • Set the page size limit to the maximum allowed (5,000 data items).

  • Delay API calls by 2 seconds each to avoid exceeding the Calls Per Minute limit.

  • For queries to be used in Excel: limit the number of pages to 200 to avoid exceeding the row limit in Excel.