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] 
      in 
        Data, 
    Loop = (page as number, AccumData as list) => 
        let 
            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})) 
        in 
            Result 
    in 
        Loop(0, {}) 
in 
    GetData

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.