Power Query API Pagination

[Rabi]

Hi Everyone, 

Below is the power query I am using to access enrollment records. (1st I am getting token then querying for records)

The number of rows that can be retrieved at a time is 50, How can I apply pagination in this query so i can retrieve entire set of enrollments.

The API supports Limit and Offset function.

let
TokenResponse = Json.Document(
        Web.Contents(
            "https://auth.go1.com",
            [RelativePath = "/oauth/token",
                Headers=[#"Content-Type" = "application/x-www-form-urlencoded"],
                Content = Text.ToBinary(
                    Uri.BuildQueryString([
                        client_id     ="ID HERE",
                        client_secret ="SECRET HERE",
                        grant_type    = "client_credentials"]))])),
    AccessToken = TokenResponse[access_token],

    url = "https://gateway.go1.com/enrollments",
    queryParams = [
        limit = "50",
        offset = "20"],
    headers = [
        #"api-version" = "2025-01-01",
        #"Content-Type" = "application/json",
        Authorization = AccessToken],
    response = Web.Contents(
        url,
        [Query = queryParams,
         Headers = headers]),
    json = Json.Document(response)
in
    json

[v-abhinavmu]

Hi @Rabi,

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[Rabi]

Hi Everyone, 

Thanks for your assistance with this. I managed to retrieve 852,320 rows of enrollment data. However, it appears that all but 659 of the retrieved rows are duplicates.

Could this be because the Scroll ID is not being passed correctly? As I understand it, each subsequent query must use the new Scroll ID returned from the previous scroll request.

let
    TokenResponse =
        Json.Document(
            Web.Contents(
                "https://auth.go1.com",
                [
                    RelativePath = "/oauth/token",
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content =
                        Text.ToBinary(
                            Uri.BuildQueryString(
                                [
                                    client_id = "IDHERE",
                                    client_secret = "SECRETHERE",
                                    grant_type = "client_credentials"
                                ]
                            )
                        )
                ]
            )
        ),
    AccessToken = TokenResponse[access_token],
    BaseUrl = "https://api.go1.com/v2/enrollments",
    FirstResponse =
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Query = [scroll = "true"],
                    Headers = [
                        #"api-version" = "2022-07-01",
                        Authorization = AccessToken
                    ]
                ]
            )
        ),
    ScrollId = try FirstResponse[_scroll_id] otherwise null,
    FirstHits = try FirstResponse[hits] otherwise {},
    GetScroll = (sid as text) =>
        try
            Json.Document(
                Web.Contents(
                    BaseUrl,
                    [
                        Query = [
                            scroll = "true",
                            scrollid = sid
                        ],
                        Headers = [
                            #"api-version" = "2022-07-01",
                            Authorization = AccessToken
                        ]
                    ]
                )
            )
        otherwise null,
    Pages =
        List.Generate(
            () => [
                Sid = ScrollId,
                Result = FirstHits,
                More = true
            ],
            each [More] = true and [Sid] <> null,
            each
                let
                    next = GetScroll([Sid]),
                    hits =
                        if next <> null
                        then try next[hits] otherwise {}
                        else {},
                    newSid =
                        if next <> null
                        then try next[_scroll_id] otherwise null
                        else null
                in
                    [
                        Sid = newSid,
                        Result = hits,
                        More = List.Count(hits) > 0 and newSid <> null
                    ],
            each [Result]
        ),
    Combined =
        List.Combine(Pages),
ResultTable =
    Table.FromRecords(
        Combined,
        null,
        MissingField.UseNull
    )
in
    ResultTable

 Below is the headers the query returns:

Total rows : 789,157 (Checked in the portal this is correct)

id

type

created_time

updated_time

lo_id

parent_enrollment_id

parent_lo_id

pass

status

user_id

utm_source

utm_content

utm_medium

utm_campaign

end_date

due_date

[vojtechsima]

Hey, @Rabi ,

check this blog post:
https://www.vojtechsima.com/post/pagination-in-power-query

this is for jira for example:

request = (offset as number, limit as number) =>  
    Json.Document(  
        Web.Contents(  
        "https://simvojtech.atlassian.net/",  
        [  
            RelativePath="/rest/api/3/search?jql=project=BI",  
            Query =   
                [  
                    startAt=Text.From(offset),  
                    maxResults=Text.From(limit)  
                ],  
            Headers=  
                [  
                    Authorization=basic_auth_string  
                ]  
        ]  
        )  
    ) 

then the paginator can look like this:

populatePagesLGEach = List.Generate(  
    ()=> 0,  
    each _ <= totalPages,  
    each _ + 1,  
    each request(_ * pageSize, pageSize)  
)

[v-abhinavmu]

Hi @Rabi,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[Rabi]

Hi, 

I appreciate your time looking into this. Unfortunately, I am still getting timeout error when the query loads. I am not sure if its becasue of the volume of data.

Thanks !

[v-abhinavmu]

Hi @Rabi,
Thanks for the update. Since you are still getting timeout errors, it may be related to the volume of data being loaded.

You could try testing the query with a smaller volume of data first to check whether the scroll pagination is working correctly before loading the full dataset.

Thank you.

[tharunkumarRTK]

You can generate a list and iterate over the list to retrieve the records as per the pagination 

try this logic

let
  
    TokenResponse =
        Json.Document(
            Web.Contents(
                "https://auth.go1.com",
                [
                    RelativePath = "/oauth/token",
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded"
                    ],
                    Content =
                        Text.ToBinary(
                            Uri.BuildQueryString(
                                [
                                    client_id = "ID HERE",
                                    client_secret = "SECRET HERE",
                                    grant_type = "client_credentials"
                                ]
                            )
                        )
                ]
            )
        ),

    AccessToken = TokenResponse[access_token],


    GetPage = (Offset as number) =>
        let
            Response =
                Json.Document(
                    Web.Contents(
                        "https://gateway.go1.com/enrollments",
                        [
                            Query = [
                                limit = "50",
                                offset = Text.From(Offset)
                            ],
                            Headers = [
                                #"api-version" = "2025-01-01",
                                #"Content-Type" = "application/json",
                                Authorization = "Bearer " & AccessToken
                            ]
                        ]
                    )
                )
        in
            Response,


    Pages =
        List.Generate(
            () => [Offset = 0, Result = GetPage(0)],
            each List.Count([Result]) > 0,
            each [
                Offset = [Offset] + 50,
                Result = GetPage([Offset] + 50)
            ],
            each [Result]
        ),


    Combined =
        List.Combine(Pages),


    ResultTable =
        Table.FromRecords(Combined)

in
    ResultTable

read my blogs here: https://www.techietips.co.in/

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Rabi]

Thanks for taking yout time to reply back !

I am getting following error:

Expression.Error: We cannot convert a value of type Record to type List.
Details:
Value=[Record]
Type=[Type]

For your Information,

My initial query returns following results:

Thanks,

[tharunkumarRTK]

thanks for testing the code

I think you can modify the code abit to make it working 

try this

let

    TokenResponse =
        Json.Document(
            Web.Contents(
                "https://auth.go1.com",
                [
                    RelativePath = "/oauth/token",
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded"
                    ],
                    Content =
                        Text.ToBinary(
                            Uri.BuildQueryString(
                                [
                                    client_id = "ID HERE",
                                    client_secret = "SECRET HERE",
                                    grant_type = "client_credentials"
                                ]
                            )
                        )
                ]
            )
        ),

    AccessToken = TokenResponse[access_token],


    GetPage = (Offset as number) =>
        let
            Response =
                Json.Document(
                    Web.Contents(
                        "https://gateway.go1.com/enrollments",
                        [
                            Query = [
                                limit = "50",
                                offset = Text.From(Offset)
                            ],
                            Headers = [
                                #"api-version" = "2025-01-01",
                                #"Content-Type" = "application/json",
                                Authorization = "Bearer " & AccessToken
                            ]
                        ]
                    )
                ),

            Hits = Response[hits]

        in
            Hits,


    Pages =
        List.Generate(
            () => [Offset = 0, Result = GetPage(0)],
            each List.Count([Result]) > 0,
            each [
                Offset = [Offset] + 50,
                Result = GetPage([Offset] + 50)
            ],
            each [Result]
        ),

    
    CombinedResults = List.Combine(Pages),


    ResultTable = Table.FromRecords(CombinedResults)

in
    ResultTable

read my blogs here: https://www.techietips.co.in/

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Rabi]

Hi, I managed to get the query to work as bwlow, I tweaked the Pages section

However I am now getting : 

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] Web.Contents failed to get contents from 'https://gateway.go1.com/enrollments?limit=50&offset=50000' (400): Bad Request. '.

let
  
    TokenResponse =
        Json.Document(
            Web.Contents(
                "https://auth.go1.com",
                [
                    RelativePath = "/oauth/token",
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content =
                        Text.ToBinary(
                            Uri.BuildQueryString(
                                [
                                    client_id = "ID HERE",
                                    client_secret = "SECRET HERE",
                                    grant_type = "client_credentials"
                                ]
                            )
                        )
                ]
            )
        ),

    AccessToken = TokenResponse[access_token],


    GetPage = (Offset as number) =>
        let
            Response =
                Json.Document(
                    Web.Contents(
                        "https://gateway.go1.com/enrollments",
                        [
                            Query = [
                                limit = "50",
                                offset = Text.From(Offset)
                            ],
                            Headers = [
                                #"api-version" = "2025-01-01",
                                #"Content-Type" = "application/json",
                                Authorization = "Bearer " & AccessToken
                            ]
                        ]
                    )
                )
        in
            Response,


Pages =
    List.Generate(
        () => [Offset = 0, Result = GetPage(0)],
        each List.Count([Result][hits]) > 0,
        each [
            Offset = [Offset] + 50,
            Result = GetPage([Offset])
        ],
        each [Result][hits]
    ),
    
    Combined =
        List.Combine(Pages),


    ResultTable =
        Table.FromRecords(Combined)

in
    ResultTable

[v-abhinavmu]

Hi @Rabi,
Thanks for reaching out to the Microsoft Fabric Community forum.

The error may be happening because the pagination loop continues requesting offsets even after all available records have been returned, which eventually causes the API to reject the request with a 400 Bad Request.

You can try using the API total value as the stopping condition so the loop only requests valid pages.

let
    TokenResponse =
        Json.Document(
            Web.Contents(
               "https://auth.go1.com",                
                   [
                    RelativePath = "/oauth/token",
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content =
                        Text.ToBinary(
                            Uri.BuildQueryString(
                                [
                                    client_id = "ID HERE",
                                    client_secret = "SECRET HERE",
                                    grant_type = "client_credentials"
                                ]
                            )
                        )
                ]
            )
        ),

    AccessToken = TokenResponse[access_token],

    Limit = 50,

    GetPage = (Offset as number) =>
        let
            Response =
                Json.Document(
                    Web.Contents(
                        "https://gateway.go1.com/enrollments",                        
                           [
                            Query = [
                                limit = Text.From(Limit),
                                offset = Text.From(Offset)
                            ],
                            Headers = [
                                #"api-version" = "2025-01-01",
                                #"Content-Type" = "application/json",
                                Authorization = "Bearer " & AccessToken
                            ]
                        ]
                    )
                )
        in
            Response,

    FirstPage = GetPage(0),

    TotalRecords = FirstPage[total],

    Pages =
        List.Generate(
            () => [
                Offset = 0,
                Result = FirstPage
            ],

            each [Offset] < TotalRecords,

            each
                let
                    NewOffset = [Offset] + Limit
                in
                    [
                        Offset = NewOffset,
                        Result = try GetPage(NewOffset) otherwise null
                    ],

            each [Result][hits]
        ),

    Combined =
        List.Combine(Pages),

    ResultTable =
        Table.FromRecords(Combined, MissingField.UseNull)

in
    ResultTable

Try this approach and see if it works better for your case.

I hope this helps. Please feel free to reach out if you have any further questions.
Thank you.

[Rabi]

Hi v-abhinavmu, 

I am getting following error:

Expression.Error: We cannot apply field access to the type Null.
Details:
Value=
Key=hits

For your Information first page looks like this:

Also there is an option to use scroll ID, I have attached the query using scroll ID, If you could look at it I would really appreciate it.

let

    TokenResponse =
        Json.Document(
            Web.Contents(
                "https://auth.go1.com",
                [
                    RelativePath = "/oauth/token",
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content =
                        Text.ToBinary(
                            Uri.BuildQueryString(
                                [
                                    client_id = "ID HERE",
                                    client_secret = "SECRET HERE",
                                    grant_type = "client_credentials"
                                ]
                            )
                        )
                ]
            )
        ),

   AccessToken = TokenResponse[access_token],

    BaseUrl = "https://api.go1.com/v2/enrollments",

    FirstResponse =
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Query = [scroll = "true"],
                    Headers = [
                        #"api-version" = "2022-07-01",
                        Authorization = AccessToken
                    ]
                ]
            )
        ),

    ScrollId = FirstResponse[_scroll_id],
    FirstHits = FirstResponse[hits],

    GetScroll = (sid as text) =>
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Query = [
                        scroll = "true",
                        scrollid = sid
                    ],
                    Headers = [
                        #"api-version" = "2022-07-01",
                        Authorization = AccessToken
                    ]
                ]
            )
        ),

    Pages =
        List.Generate(
            () => [Sid = ScrollId, Result = FirstHits, More = true],

            each [More] = true and [Result] <> null,

            each
                let
                    next = GetScroll([Sid]),
                    hits = try next[hits] otherwise {},
                    newSid = try next[_scroll_id] otherwise null
                in
                    [
                        Sid = newSid,
                        Result = hits,
                        More = List.Count(hits) > 0 and newSid <> null
                    ],

            each [Result]
        ),

    Combined =
        List.Combine(Pages),

    ResultTable =
        Table.FromRecords(Combined)

in
    ResultTable

[v-abhinavmu]

Hi @Rabi,
Thanks for the update and for sharing the scroll ID approach.

The error may be happening because one of the scroll requests is returning null, and the query is still trying to access the hits field from that response. Since the API supports scroll pagination, that would generally be a better option for larger datasets compared to offset pagination.

let
    TokenResponse =
        Json.Document(
            Web.Contents(
                "https://auth.go1.com",
                [
                    RelativePath = "/oauth/token",
                    Headers = [
                        #"Content-Type" = "application/x-www-form-urlencoded",
                        Accept = "application/json"
                    ],
                    Content =
                        Text.ToBinary(
                            Uri.BuildQueryString(
                                [
                                    client_id = "ID HERE",
                                    client_secret = "SECRET HERE",
                                    grant_type = "client_credentials"
                                ]
                            )
                        )
                ]
            )
        ),
    AccessToken = TokenResponse[access_token],
    BaseUrl = "https://api.go1.com/v2/enrollments",
    FirstResponse =
        Json.Document(
            Web.Contents(
                BaseUrl,
                [
                    Query = [scroll = "true"],
                    Headers = [
                        #"api-version" = "2022-07-01",
                        Authorization = AccessToken
                    ]
                ]
            )
        ),
    ScrollId = try FirstResponse[_scroll_id] otherwise null,
    FirstHits = try FirstResponse[hits] otherwise {},
    GetScroll = (sid as text) =>
        try
            Json.Document(
                Web.Contents(
                    BaseUrl,
                    [
                        Query = [
                            scroll = "true",
                            scrollid = sid
                        ],
                        Headers = [
                            #"api-version" = "2022-07-01",
                            Authorization = AccessToken
                        ]
                    ]
                )
            )
        otherwise null,
    Pages =
        List.Generate(
            () => [
                Sid = ScrollId,
                Result = FirstHits,
                More = true
            ],
            each [More] = true and [Sid] <> null,
            each
                let
                    next = GetScroll([Sid]),
                    hits =
                        if next <> null
                        then try next[hits] otherwise {}
                        else {},
                    newSid =
                        if next <> null
                        then try next[_scroll_id] otherwise null
                        else null
                in
                    [
                        Sid = newSid,
                        Result = hits,
                        More = List.Count(hits) > 0 and newSid <> null
                    ],
            each [Result]
        ),
    Combined =
        List.Combine(Pages),
    ResultTable =
        Table.FromRecords(Combined, MissingField.UseNull)
in
    ResultTable

Try this approach and see if it works.

For reference, you can also check a similar thread here.

Solved: Expression.Error: We cannot apply field access to ... - Microsoft Fabric Community

Thank you.