I'm trying to get all of the data from an API into a single table but it handles pagination using Offset and Limit. The JSON provides a URL path for first, previous, next, and last. I just don't have enough coding knowledge to get this working. I am using PowerQuery to pull this data into Excel.

The closest article I found that is like what I am trying to do is :this stackoverflow question

The JSON provides data as follows:

{
  "totalResults": 120,
  "offset": 0,
  "limit": 20,
  "first": "/mob/api/v7/costs/business-unit-transactions?endDate=1667192283000&startDate=1664600283000&limit=20&offset=0",
  "previous": null,
  "next": "/mob/api/v7/costs/business-unit-transactions?endDate=1667192283000&startDate=1664600283000&limit=20&offset=20",
  "last": "/mob/api/v7/costs/business-unit-transactions?endDate=1667192283000&startDate=1664600283000&limit=20&offset=120",
  "result": [
    {
      "activityId": "OP:29086",
      "postedId": 8260,
      
    },
    {
      "activityId": "OP:29086",
      "postedId": 8261,
  
    }
  ]
}

The actual API endpoint I am using has a limit of 200 records per pull. Any help getting the code working is very much appreciated.

I have tried pulling data and have successfully been able to pull the first 200 records. Just haven't figured out how to pull all of the data and compile into one table before splitting into columns. Below is the code that I have currently. I have the URL that I start with specified in a named cell within the sheet. That way I can update the start and end date in the workbook, which updates the URL request, then refresh the query to get data.

let
    Source = Web.Contents(Excel.CurrentWorkbook(){[Name="SandboxURL"]}[Content]{0}[Column1]),
    #"Imported JSON" = Json.Document(Source,1252),
    Convert = Record.ToTable(#"Imported JSON"),
    Value = Convert{7}[Value],
    #"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"activityId", "postedId"})

in
    #"Expanded Column1"

I'm trying to get all of the data from an API into a single table but it handles pagination using Offset and Limit. The JSON provides a URL path for first, previous, next, and last. I just don't have enough coding knowledge to get this working. I am using PowerQuery to pull this data into Excel.

The closest article I found that is like what I am trying to do is :this stackoverflow question

The JSON provides data as follows:

{
  "totalResults": 120,
  "offset": 0,
  "limit": 20,
  "first": "/mob/api/v7/costs/business-unit-transactions?endDate=1667192283000&startDate=1664600283000&limit=20&offset=0",
  "previous": null,
  "next": "/mob/api/v7/costs/business-unit-transactions?endDate=1667192283000&startDate=1664600283000&limit=20&offset=20",
  "last": "/mob/api/v7/costs/business-unit-transactions?endDate=1667192283000&startDate=1664600283000&limit=20&offset=120",
  "result": [
    {
      "activityId": "OP:29086",
      "postedId": 8260,
      
    },
    {
      "activityId": "OP:29086",
      "postedId": 8261,
  
    }
  ]
}

The actual API endpoint I am using has a limit of 200 records per pull. Any help getting the code working is very much appreciated.

I have tried pulling data and have successfully been able to pull the first 200 records. Just haven't figured out how to pull all of the data and compile into one table before splitting into columns. Below is the code that I have currently. I have the URL that I start with specified in a named cell within the sheet. That way I can update the start and end date in the workbook, which updates the URL request, then refresh the query to get data.

let
    Source = Web.Contents(Excel.CurrentWorkbook(){[Name="SandboxURL"]}[Content]{0}[Column1]),
    #"Imported JSON" = Json.Document(Source,1252),
    Convert = Record.ToTable(#"Imported JSON"),
    Value = Convert{7}[Value],
    #"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"activityId", "postedId"})

in
    #"Expanded Column1"

• excel
• pagination
• powerquery

1 Answer 1

Ended up working on this for a few more days and used AI to help debug my code. Eventually got it working correctly. Posting this here in case anybody else needs the working code.

let
    BaseURL = "https://sandbox.vintrace",
    ApiLimit = Excel.CurrentWorkbook(){[Name="Limit"]}[Content]{0}[Column1],
    Source = Json.Document(Web.Contents(Excel.CurrentWorkbook(){[Name="SandboxURL"]}[Content]{0}[Column1])),
    NextPage = Source[#"first"],

    GetJson = (Url) =>
        let
            Json = Json.Document(Web.Contents(Url), 1252)
        in
            Json,   

    GetCount = () =>
        let
            Count = Source[totalResults]
        in
            Count,

    // Recursive function to get pages
    GetPagesRecursively = (Url) =>
        let
            Json = GetJson(Url),
            Results = Json[#"results"],
            NextUrl = try Json[#"next"] otherwise null,  // Try to get the next URL
            MoreResults = if NextUrl <> null then @GetPagesRecursively(BaseURL & NextUrl) else {}  // If next exists, recurse, else end
        in
            List.Combine({Results, MoreResults}),

    // Start the recursive fetch from the first page URL
    Pages = GetPagesRecursively(BaseURL & NextPage),
    
    // Convert list of records to a table
    Data = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Query1" = Data

in
    #"Query1"