Power Query Pagination

wildcat89 0
Viewed 28

I'm new to Power Query. I'm looking to create an iterative pagination script for my business case.

A quick note, I cannot provide the API key or Endpoint that I'm using, so you will not be able to test it on your end, I will have to test the code on my side. I am seeking some suggested code, which I will test on my side.

I have a working request code for one page shown below (note all of the Expand... stuff which is screwing me up in figuring out how to paginate this request). The results are of email related data for context:

// Working for 1 page
let
    // Define some variables
    _url = "https://www.randomURLNOTWORKING.com/events?before=9999999999",
    _headers = [authorization="Bearer XXXXXXXXXXXXXXXXX"],

    // Request the JSON response
    Source = Json.Document(Web.Contents(_url, [Headers=_headers])),
    
    // Unpack the response
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded _pagination" = Table.ExpandRecordColumn(#"Converted to Table", "_pagination", {"next"}, {"_pagination.next"}),
    #"Expanded _links" = Table.ExpandRecordColumn(#"Expanded _pagination", "_links", {"self"}, {"_links.self"}),
    #"Expanded _results" = Table.ExpandListColumn(#"Expanded _links", "_results"),
    #"Expanded _results1" = Table.ExpandRecordColumn(#"Expanded _results", "_results", {"_links", "id", "type", "emitted_at", "conversation", "source", "target"}, {"_results._links", "_results.id", "_results.type", "_results.emitted_at", "_results.conversation", "_results.source", "_results.target"}),
    #"Expanded _results._links" = Table.ExpandRecordColumn(#"Expanded _results1", "_results._links", {"self"}, {"_results._links.self"}),
    #"Expanded _results.conversation" = Table.ExpandRecordColumn(#"Expanded _results._links", "_results.conversation", {"_links", "id", "subject", "status", "assignee", "recipient", "tags", "links", "created_at", "is_private", "scheduled_reminders", "metadata"}, {"_results.conversation._links", "_results.conversation.id", "_results.conversation.subject", "_results.conversation.status", "_results.conversation.assignee", "_results.conversation.recipient", "_results.conversation.tags", "_results.conversation.links", "_results.conversation.created_at", "_results.conversation.is_private", "_results.conversation.scheduled_reminders", "_results.conversation.metadata"}),
    #"Expanded _results.conversation._links" = Table.ExpandRecordColumn(#"Expanded _results.conversation", "_results.conversation._links", {"self", "related"}, {"_results.conversation._links.self", "_results.conversation._links.related"}),
    #"Expanded _results.conversation._links.related" = Table.ExpandRecordColumn(#"Expanded _results.conversation._links", "_results.conversation._links.related", {"events", "followers", "messages", "comments", "inboxes", "last_message"}, {"_results.conversation._links.related.events", "_results.conversation._links.related.followers", "_results.conversation._links.related.messages", "_results.conversation._links.related.comments", "_results.conversation._links.related.inboxes", "_results.conversation._links.related.last_message"}),
    #"Expanded _results.conversation.recipient" = Table.ExpandRecordColumn(#"Expanded _results.conversation._links.related", "_results.conversation.recipient", {"_links", "name", "handle", "role"}, {"_results.conversation.recipient._links", "_results.conversation.recipient.name", "_results.conversation.recipient.handle", "_results.conversation.recipient.role"}),
    #"Expanded _results.conversation.recipient._links" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient", "_results.conversation.recipient._links", {"related"}, {"_results.conversation.recipient._links.related"}),
    #"Expanded _results.conversation.recipient._links.related" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient._links", "_results.conversation.recipient._links.related", {"contact"}, {"_results.conversation.recipient._links.related.contact"}),
    #"Expanded _results.conversation.metadata" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient._links.related", "_results.conversation.metadata", {}, {}),
    #"Expanded _results.source" = Table.ExpandRecordColumn(#"Expanded _results.conversation.metadata", "_results.source", {"_meta", "data"}, {"_results.source._meta", "_results.source.data"}),
    #"Expanded _results.source._meta" = Table.ExpandRecordColumn(#"Expanded _results.source", "_results.source._meta", {"type"}, {"_results.source._meta.type"}),
    #"Expanded _results.target" = Table.ExpandRecordColumn(#"Expanded _results.source._meta", "_results.target", {"_meta", "data"}, {"_results.target._meta", "_results.target.data"}),
    #"Expanded _results.target._meta" = Table.ExpandRecordColumn(#"Expanded _results.target", "_results.target._meta", {"type"}, {"_results.target._meta.type"}),
    #"Expanded _results.target.data" = Table.ExpandRecordColumn(#"Expanded _results.target._meta", "_results.target.data", {"_links", "id", "type", "is_inbound", "name", "highlight", "is_private", "is_visible_in_conversation_lists", "updated_at", "created_at", "blurb", "body", "text", "error_type", "version", "subject", "draft_mode", "metadata", "posted_at", "author", "recipients", "attachments", "signature", "is_draft", "email", "username", "first_name", "last_name", "is_admin", "is_available", "is_blocked", "custom_fields"}, {"_results.target.data._links", "_results.target.data.id", "_results.target.data.type", "_results.target.data.is_inbound", "_results.target.data.name", "_results.target.data.highlight", "_results.target.data.is_private", "_results.target.data.is_visible_in_conversation_lists", "_results.target.data.updated_at", "_results.target.data.created_at", "_results.target.data.blurb", "_results.target.data.body", "_results.target.data.text", "_results.target.data.error_type", "_results.target.data.version", "_results.target.data.subject", "_results.target.data.draft_mode", "_results.target.data.metadata", "_results.target.data.posted_at", "_results.target.data.author", "_results.target.data.recipients", "_results.target.data.attachments", "_results.target.data.signature", "_results.target.data.is_draft", "_results.target.data.email", "_results.target.data.username", "_results.target.data.first_name", "_results.target.data.last_name", "_results.target.data.is_admin", "_results.target.data.is_available", "_results.target.data.is_blocked", "_results.target.data.custom_fields"}),
    #"Expanded _results.target.data._links" = Table.ExpandRecordColumn(#"Expanded _results.target.data", "_results.target.data._links", {"self", "related"}, {"_results.target.data._links.self", "_results.target.data._links.related"}),
    #"Expanded _results.target.data._links.related" = Table.ExpandRecordColumn(#"Expanded _results.target.data._links", "_results.target.data._links.related", {"conversation", "message_seen", "mentions", "inboxes", "conversations", "owner", "parent_tag", "children", "message_replied_to"}, {"_results.target.data._links.related.conversation", "_results.target.data._links.related.message_seen", "_results.target.data._links.related.mentions", "_results.target.data._links.related.inboxes", "_results.target.data._links.related.conversations", "_results.target.data._links.related.owner", "_results.target.data._links.related.parent_tag", "_results.target.data._links.related.children", "_results.target.data._links.related.message_replied_to"}),
    #"Expanded _results.target.data.metadata" = Table.ExpandRecordColumn(#"Expanded _results.target.data._links.related", "_results.target.data.metadata", {}, {}),
    #"Expanded _results.target.data.custom_fields" = Table.ExpandRecordColumn(#"Expanded _results.target.data.metadata", "_results.target.data.custom_fields", {}, {}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded _results.target.data.custom_fields",{{"_pagination.next", type text}, {"_links.self", type text}, {"_results._links.self", type text}, {"_results.id", type text}, {"_results.type", type text}, {"_results.emitted_at", type number}, {"_results.conversation._links.self", type text}, {"_results.conversation._links.related.events", type text}, {"_results.conversation._links.related.followers", type text}, {"_results.conversation._links.related.messages", type text}, {"_results.conversation._links.related.comments", type text}, {"_results.conversation._links.related.inboxes", type text}, {"_results.conversation._links.related.last_message", type text}, {"_results.conversation.id", type text}, {"_results.conversation.subject", type text}, {"_results.conversation.status", type text}, {"_results.conversation.assignee", type any}, {"_results.conversation.recipient._links.related.contact", type text}, {"_results.conversation.recipient.name", type text}, {"_results.conversation.recipient.handle", type text}, {"_results.conversation.recipient.role", type text}, {"_results.conversation.tags", type any}, {"_results.conversation.links", type any}, {"_results.conversation.created_at", type number}, {"_results.conversation.is_private", type logical}, {"_results.conversation.scheduled_reminders", type any}, {"_results.source._meta.type", type text}, {"_results.source.data", type any}, {"_results.target._meta.type", type text}, {"_results.target.data._links.self", type text}, {"_results.target.data._links.related.conversation", type text}, {"_results.target.data._links.related.message_seen", type text}, {"_results.target.data._links.related.mentions", type text}, {"_results.target.data._links.related.inboxes", type text}, {"_results.target.data._links.related.conversations", type text}, {"_results.target.data._links.related.owner", type text}, {"_results.target.data._links.related.parent_tag", type any}, {"_results.target.data._links.related.children", type any}, {"_results.target.data._links.related.message_replied_to", type text}, {"_results.target.data.id", type text}, {"_results.target.data.type", type text}, {"_results.target.data.is_inbound", type logical}, {"_results.target.data.name", type text}, {"_results.target.data.highlight", type any}, {"_results.target.data.is_private", type logical}, {"_results.target.data.is_visible_in_conversation_lists", type logical}, {"_results.target.data.updated_at", type number}, {"_results.target.data.created_at", type number}, {"_results.target.data.blurb", type text}, {"_results.target.data.body", type text}, {"_results.target.data.text", type text}, {"_results.target.data.error_type", type any}, {"_results.target.data.version", type any}, {"_results.target.data.subject", type text}, {"_results.target.data.draft_mode", type any}, {"_results.target.data.posted_at", type number}, {"_results.target.data.author", type any}, {"_results.target.data.recipients", type any}, {"_results.target.data.attachments", type any}, {"_results.target.data.signature", type any}, {"_results.target.data.is_draft", type logical}, {"_results.target.data.email", type text}, {"_results.target.data.username", type text}, {"_results.target.data.first_name", type text}, {"_results.target.data.last_name", type text}, {"_results.target.data.is_admin", type logical}, {"_results.target.data.is_available", type logical}, {"_results.target.data.is_blocked", type logical}})
in
    #"Changed Type"

Please note that I have looked at all of the articles linked below, but am still having trouble getting the correct syntax as this is my first Power Query project. I have a pseudo-represented code of a mod from this article shown below:

// Attempt at pagination
let

    // Define some variables
    _url = "https://www.randomURLNOTWORKING.com/events?before=9999999999",
    _headers = [authorization="Bearer XXXXXXXXXXXXXXXXX"],
    _iterations = 3,

    // Function - Get the results of current page
    FnGetOnePage =
        (_url) as record =>
            let
                // Get the JSON response of the current page
                Source = Json.Document(Web.Contents(_url, [Headers=_headers])),
                ///////////////////////////////// Below is not appropriate for my use case
                //data = Source[data],
                //next = Source[paging][next],

                // Return the "data" (I'm assuming it should be in unpacked form?) and the "next" pagination link
                res = [Data=data, Next=next]
            in
                res,

    // Function - Generate a list of results
    // Not sure if this is what my use case needs either but leaving as pseudo
    GeneratedList =
        List.Generate(
            ()=>[i=0, res = FnGetOnePage(_url)],
            each [i]<_iterations and [res][Data]<>null,
            each [i=[i]+1, res = FnGetOnePage([res][Next])],
            each [res][Data]),

    // Unpack all of the results
    #"Converted to Table" = Table.FromRecords(GeneratedList),
    #"Expanded _pagination" = Table.ExpandRecordColumn(#"Converted to Table", "_pagination", {"next"}, {"_pagination.next"}),
    #"Expanded _links" = Table.ExpandRecordColumn(#"Expanded _pagination", "_links", {"self"}, {"_links.self"}),
    #"Expanded _results" = Table.ExpandListColumn(#"Expanded _links", "_results"),
    #"Expanded _results1" = Table.ExpandRecordColumn(#"Expanded _results", "_results", {"_links", "id", "type", "emitted_at", "conversation", "source", "target"}, {"_results._links", "_results.id", "_results.type", "_results.emitted_at", "_results.conversation", "_results.source", "_results.target"}),
    #"Expanded _results._links" = Table.ExpandRecordColumn(#"Expanded _results1", "_results._links", {"self"}, {"_results._links.self"}),
    #"Expanded _results.conversation" = Table.ExpandRecordColumn(#"Expanded _results._links", "_results.conversation", {"_links", "id", "subject", "status", "assignee", "recipient", "tags", "links", "created_at", "is_private", "scheduled_reminders", "metadata"}, {"_results.conversation._links", "_results.conversation.id", "_results.conversation.subject", "_results.conversation.status", "_results.conversation.assignee", "_results.conversation.recipient", "_results.conversation.tags", "_results.conversation.links", "_results.conversation.created_at", "_results.conversation.is_private", "_results.conversation.scheduled_reminders", "_results.conversation.metadata"}),
    #"Expanded _results.conversation._links" = Table.ExpandRecordColumn(#"Expanded _results.conversation", "_results.conversation._links", {"self", "related"}, {"_results.conversation._links.self", "_results.conversation._links.related"}),
    #"Expanded _results.conversation._links.related" = Table.ExpandRecordColumn(#"Expanded _results.conversation._links", "_results.conversation._links.related", {"events", "followers", "messages", "comments", "inboxes", "last_message"}, {"_results.conversation._links.related.events", "_results.conversation._links.related.followers", "_results.conversation._links.related.messages", "_results.conversation._links.related.comments", "_results.conversation._links.related.inboxes", "_results.conversation._links.related.last_message"}),
    #"Expanded _results.conversation.recipient" = Table.ExpandRecordColumn(#"Expanded _results.conversation._links.related", "_results.conversation.recipient", {"_links", "name", "handle", "role"}, {"_results.conversation.recipient._links", "_results.conversation.recipient.name", "_results.conversation.recipient.handle", "_results.conversation.recipient.role"}),
    #"Expanded _results.conversation.recipient._links" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient", "_results.conversation.recipient._links", {"related"}, {"_results.conversation.recipient._links.related"}),
    #"Expanded _results.conversation.recipient._links.related" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient._links", "_results.conversation.recipient._links.related", {"contact"}, {"_results.conversation.recipient._links.related.contact"}),
    #"Expanded _results.conversation.metadata" = Table.ExpandRecordColumn(#"Expanded _results.conversation.recipient._links.related", "_results.conversation.metadata", {}, {}),
    #"Expanded _results.source" = Table.ExpandRecordColumn(#"Expanded _results.conversation.metadata", "_results.source", {"_meta", "data"}, {"_results.source._meta", "_results.source.data"}),
    #"Expanded _results.source._meta" = Table.ExpandRecordColumn(#"Expanded _results.source", "_results.source._meta", {"type"}, {"_results.source._meta.type"}),
    #"Expanded _results.target" = Table.ExpandRecordColumn(#"Expanded _results.source._meta", "_results.target", {"_meta", "data"}, {"_results.target._meta", "_results.target.data"}),
    #"Expanded _results.target._meta" = Table.ExpandRecordColumn(#"Expanded _results.target", "_results.target._meta", {"type"}, {"_results.target._meta.type"}),
    #"Expanded _results.target.data" = Table.ExpandRecordColumn(#"Expanded _results.target._meta", "_results.target.data", {"_links", "id", "type", "is_inbound", "name", "highlight", "is_private", "is_visible_in_conversation_lists", "updated_at", "created_at", "blurb", "body", "text", "error_type", "version", "subject", "draft_mode", "metadata", "posted_at", "author", "recipients", "attachments", "signature", "is_draft", "email", "username", "first_name", "last_name", "is_admin", "is_available", "is_blocked", "custom_fields"}, {"_results.target.data._links", "_results.target.data.id", "_results.target.data.type", "_results.target.data.is_inbound", "_results.target.data.name", "_results.target.data.highlight", "_results.target.data.is_private", "_results.target.data.is_visible_in_conversation_lists", "_results.target.data.updated_at", "_results.target.data.created_at", "_results.target.data.blurb", "_results.target.data.body", "_results.target.data.text", "_results.target.data.error_type", "_results.target.data.version", "_results.target.data.subject", "_results.target.data.draft_mode", "_results.target.data.metadata", "_results.target.data.posted_at", "_results.target.data.author", "_results.target.data.recipients", "_results.target.data.attachments", "_results.target.data.signature", "_results.target.data.is_draft", "_results.target.data.email", "_results.target.data.username", "_results.target.data.first_name", "_results.target.data.last_name", "_results.target.data.is_admin", "_results.target.data.is_available", "_results.target.data.is_blocked", "_results.target.data.custom_fields"}),
    #"Expanded _results.target.data._links" = Table.ExpandRecordColumn(#"Expanded _results.target.data", "_results.target.data._links", {"self", "related"}, {"_results.target.data._links.self", "_results.target.data._links.related"}),
    #"Expanded _results.target.data._links.related" = Table.ExpandRecordColumn(#"Expanded _results.target.data._links", "_results.target.data._links.related", {"conversation", "message_seen", "mentions", "inboxes", "conversations", "owner", "parent_tag", "children", "message_replied_to"}, {"_results.target.data._links.related.conversation", "_results.target.data._links.related.message_seen", "_results.target.data._links.related.mentions", "_results.target.data._links.related.inboxes", "_results.target.data._links.related.conversations", "_results.target.data._links.related.owner", "_results.target.data._links.related.parent_tag", "_results.target.data._links.related.children", "_results.target.data._links.related.message_replied_to"}),
    #"Expanded _results.target.data.metadata" = Table.ExpandRecordColumn(#"Expanded _results.target.data._links.related", "_results.target.data.metadata", {}, {}),
    #"Expanded _results.target.data.custom_fields" = Table.ExpandRecordColumn(#"Expanded _results.target.data.metadata", "_results.target.data.custom_fields", {}, {}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded _results.target.data.custom_fields",{{"_pagination.next", type text}, {"_links.self", type text}, {"_results._links.self", type text}, {"_results.id", type text}, {"_results.type", type text}, {"_results.emitted_at", type number}, {"_results.conversation._links.self", type text}, {"_results.conversation._links.related.events", type text}, {"_results.conversation._links.related.followers", type text}, {"_results.conversation._links.related.messages", type text}, {"_results.conversation._links.related.comments", type text}, {"_results.conversation._links.related.inboxes", type text}, {"_results.conversation._links.related.last_message", type text}, {"_results.conversation.id", type text}, {"_results.conversation.subject", type text}, {"_results.conversation.status", type text}, {"_results.conversation.assignee", type any}, {"_results.conversation.recipient._links.related.contact", type text}, {"_results.conversation.recipient.name", type text}, {"_results.conversation.recipient.handle", type text}, {"_results.conversation.recipient.role", type text}, {"_results.conversation.tags", type any}, {"_results.conversation.links", type any}, {"_results.conversation.created_at", type number}, {"_results.conversation.is_private", type logical}, {"_results.conversation.scheduled_reminders", type any}, {"_results.source._meta.type", type text}, {"_results.source.data", type any}, {"_results.target._meta.type", type text}, {"_results.target.data._links.self", type text}, {"_results.target.data._links.related.conversation", type text}, {"_results.target.data._links.related.message_seen", type text}, {"_results.target.data._links.related.mentions", type text}, {"_results.target.data._links.related.inboxes", type text}, {"_results.target.data._links.related.conversations", type text}, {"_results.target.data._links.related.owner", type text}, {"_results.target.data._links.related.parent_tag", type any}, {"_results.target.data._links.related.children", type any}, {"_results.target.data._links.related.message_replied_to", type text}, {"_results.target.data.id", type text}, {"_results.target.data.type", type text}, {"_results.target.data.is_inbound", type logical}, {"_results.target.data.name", type text}, {"_results.target.data.highlight", type any}, {"_results.target.data.is_private", type logical}, {"_results.target.data.is_visible_in_conversation_lists", type logical}, {"_results.target.data.updated_at", type number}, {"_results.target.data.created_at", type number}, {"_results.target.data.blurb", type text}, {"_results.target.data.body", type text}, {"_results.target.data.text", type text}, {"_results.target.data.error_type", type any}, {"_results.target.data.version", type any}, {"_results.target.data.subject", type text}, {"_results.target.data.draft_mode", type any}, {"_results.target.data.posted_at", type number}, {"_results.target.data.author", type any}, {"_results.target.data.recipients", type any}, {"_results.target.data.attachments", type any}, {"_results.target.data.signature", type any}, {"_results.target.data.is_draft", type logical}, {"_results.target.data.email", type text}, {"_results.target.data.username", type text}, {"_results.target.data.first_name", type text}, {"_results.target.data.last_name", type text}, {"_results.target.data.is_admin", type logical}, {"_results.target.data.is_available", type logical}, {"_results.target.data.is_blocked", type logical}})
in
    #"Changed Type"

Obviously this code does not work, and I think this is over complicating what I'm looking to do.

A thing to note that might help the developer is that the NEXT request DOES NOT make use of a "page number" or anything, so I don't need to access i in the iterative loop at all. Basically the flow should look like this:

  1. Start a for loop up to _iterations in duration
  2. Request the content from the _url
  3. Unpack the details of the response using all those #'s to get it into proper table format (if necessary at this stage?)
  4. Request the content from the _pagination.next link from the previous request.
  5. Repeat steps 3-4 until iteration is done.
  6. Review the table
  7. Profit!

So the code from the article I'm sourcing is likely NOT the best way to go about this, and the code from the other articles seems like overkill. These are the articles I've looked at so far, and I have a conceptual understanding of what needs to happen, but syntactically I'm just not there yet.

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

https://gist.github.com/MarkTiedemann/f667c75cc3d054b9b2bce25ea08bc631

https://comertechnology.com/cw-manage-getting-started-with-powerbi-the-update/?fbclid=IwAR0ReCaaX7KgXR8CsozWsbIzCH1vzLXM0C3ZQFAbBKoVjGAUEJImNnj9kLo

https://community.powerbi.com/t5/Desktop/Pagination-of-a-REST-API-in-Power-Query-using-M/td-p/1901067

powerquery pagination powerbi
0 Answers
Related