Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
adwilliams
Frequent Visitor

Offset Pagination in Power Query

Hi! 

I am trying to paginate my request which uses offset pagination. My response looks like this. The response.countEmail is the total number of items and I would like to set my limit to 100. 

{
    "meta": {
        "uuid": "01838f28-8c7d-c2b1-a515-827392568e57",
        "errors": []
    },
    "response": {
        "countEmail": 99356,
        "countSMS": 0,
        "sent": 176775,
        "opened": 77419,
        "clicked": 14406,
        "responded": 5924,
        "invitations": [
            {
                "firstName": "XXX",
                "lastName": "XXX",
                "contact": "XXX",
                "templateId": 2164,
                "status": "ACCEPTED",
                "entity": {
                    "id": "200"
                },
                "type": "EMAIL",
                "requested": 1664201450000,
                "sent": 1664201475000,
                "opened": 1664536963000,
                "invitationUid": "1a41ae53-d7d9-46c7-b53a-6b1a60a0fcb5",
                "feedbackUrl": "XXX"
            }
...

 

What is the proper way to set the offset = to the previous limit + 100 until all of the countEmails have ben returned?

 

I have already tried a solution similar to this one https://community.powerbi.com/t5/Desktop/Automatic-pagination-with-incrementing-offset/m-p/1797504#M... and it is not working for my query. 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

So you know your total count, and your desired return size. You don't need to worry about the previous limit.  Use a generator to craft all the URLs, then call all the URLs, then combine the results.

 

Note that the list starts at 100 since you already fetched the entry at offset 0.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrQ0NjVTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CountEmail = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CountEmail", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let c = [CountEmail] in List.Generate(()=>100,each _ < c, each _+ 100)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "URL", each "https:///api.site.com?offset=" & Text.From([Custom]))
in
    #"Added Custom1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

So you know your total count, and your desired return size. You don't need to worry about the previous limit.  Use a generator to craft all the URLs, then call all the URLs, then combine the results.

 

Note that the list starts at 100 since you already fetched the entry at offset 0.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrQ0NjVTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CountEmail = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CountEmail", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let c = [CountEmail] in List.Generate(()=>100,each _ < c, each _+ 100)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "URL", each "https:///api.site.com?offset=" & Text.From([Custom]))
in
    #"Added Custom1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Thanks, @lbendlin.

 

Are you suggesting to then create a second query to make all of the calls from the generated URLs and combine them into a single table? Is it possile to make all of the calls in a single query?

 

My only concern with this approach is if the number of responses increases, is the second query going to be dynamic to handle (or add) additional URLs generated by the query you provided?

Yes, that is what I am suggesting.  If you are concerned about the number of calls then you can increase the return row count and the offset.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.