I am directly connecting to a data source using an API. I am trying to overcome to 1k record limitation. I am wondering if there is a better way to implement pagination than what I am currently doing. Currently, I have 10 queries. Each query is pulling individual pages 1-10. I then amended all the queries into one. I am concerned this may cause performance issues. Is there a better way I can call the next page without creating a separate query? My 1st query is below. My 2nd query is the same but page 2 etc.... (highlighted in red)
let
Source = Json.Document(Web.Contents("https://" & "api-na." & "myconnectwise.net/" & "v4_6_release/apis/3.0/" & "/project/tickets?pageSize=1000&page=1&orderBy=dateEntered desc", [Headers=[Authorization="Basic TOKEN", clientID="CLIENTID"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "ProjectTickets"}}),
#"Expanded ProjectTickets" = Table.ExpandRecordColumn(#"Renamed Columns", "ProjectTickets", {"id", "summary", "isIssueFlag", "board", "status", "project", "phase", "wbsCode", "company", "site", "siteName", "addressLine1", "city", "stateIdentifier", "zip", "country", "contact", "contactName", "contactPhoneNumber", "contactEmailAddress", "priority", "serviceLocation", "source", "budgetHours", "opportunity", "allowAllClientsPortalView", "customerUpdatedFlag", "automaticEmailContactFlag", "automaticEmailResourceFlag", "automaticEmailCcFlag", "automaticEmailCc", "closedFlag", "approved", "subBillingMethod", "billTime", "billExpenses", "billProducts", "location", "department", "mobileGuid", "currency", "_info", "customFields", "requiredDate", "actualHours", "closedDate", "closedBy", "owner", "resources", "estimatedStartDate", "addressLine2", "workRole", "workType", "contactPhoneExtension", "type", "subType", "duration", "item", "agreement", "predecessorType", "predecessorId", "predecessorClosedFlag", "lagDays", "lagNonworkingDaysFlag"}, {"ProjectTickets.id", "ProjectTickets.summary", "ProjectTickets.isIssueFlag", "ProjectTickets.board", "ProjectTickets.status", "ProjectTickets.project", "ProjectTickets.phase", "ProjectTickets.wbsCode", "ProjectTickets.company", "ProjectTickets.site", "ProjectTickets.siteName", "ProjectTickets.addressLine1", "ProjectTickets.city", "ProjectTickets.stateIdentifier", "ProjectTickets.zip", "ProjectTickets.country", "ProjectTickets.contact", "ProjectTickets.contactName", "ProjectTickets.contactPhoneNumber", "ProjectTickets.contactEmailAddress", "ProjectTickets.priority", "ProjectTickets.serviceLocation", "ProjectTickets.source", "ProjectTickets.budgetHours", "ProjectTickets.opportunity", "ProjectTickets.allowAllClientsPortalView", "ProjectTickets.customerUpdatedFlag", "ProjectTickets.automaticEmailContactFlag", "ProjectTickets.automaticEmailResourceFlag", "ProjectTickets.automaticEmailCcFlag", "ProjectTickets.automaticEmailCc", "ProjectTickets.closedFlag", "ProjectTickets.approved", "ProjectTickets.subBillingMethod", "ProjectTickets.billTime", "ProjectTickets.billExpenses", "ProjectTickets.billProducts", "ProjectTickets.location", "ProjectTickets.department", "ProjectTickets.mobileGuid", "ProjectTickets.currency", "ProjectTickets._info", "ProjectTickets.customFields", "ProjectTickets.requiredDate", "ProjectTickets.actualHours", "ProjectTickets.closedDate", "ProjectTickets.closedBy", "ProjectTickets.owner", "ProjectTickets.resources", "ProjectTickets.estimatedStartDate", "ProjectTickets.addressLine2", "ProjectTickets.workRole", "ProjectTickets.workType", "ProjectTickets.contactPhoneExtension", "ProjectTickets.type", "ProjectTickets.subType", "ProjectTickets.duration", "ProjectTickets.item", "ProjectTickets.agreement", "ProjectTickets.predecessorType", "ProjectTickets.predecessorId", "ProjectTickets.predecessorClosedFlag", "ProjectTickets.lagDays", "ProjectTickets.lagNonworkingDaysFlag"}),
#"Expanded ProjectTickets._info" = Table.ExpandRecordColumn(#"Expanded ProjectTickets", "ProjectTickets._info", {"lastUpdated", "dateEntered"}, {"ProjectTickets._info.lastUpdated", "ProjectTickets._info.dateEntered"}),
#"Expanded ProjectTickets.owner" = Table.ExpandRecordColumn(#"Expanded ProjectTickets._info", "ProjectTickets.owner", {"id", "identifier", "name"}, {"ProjectTickets.owner.id", "ProjectTickets.owner.identifier", "ProjectTickets.owner.name"}),
#"Expanded ProjectTickets.workRole" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.owner", "ProjectTickets.workRole", {"id", "name"}, {"ProjectTickets.workRole.id", "ProjectTickets.workRole.name"}),
#"Expanded ProjectTickets.opportunity" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.workRole", "ProjectTickets.opportunity", {"id", "name"}, {"ProjectTickets.opportunity.id", "ProjectTickets.opportunity.name"}),
#"Expanded ProjectTickets.type" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.opportunity", "ProjectTickets.type", {"id", "name"}, {"ProjectTickets.type.id", "ProjectTickets.type.name"}),
#"Expanded ProjectTickets.priority" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.type", "ProjectTickets.priority", {"id", "name", "sort"}, {"ProjectTickets.priority.id", "ProjectTickets.priority.name", "ProjectTickets.priority.sort"}),
#"Expanded ProjectTickets.country" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.priority", "ProjectTickets.country", {"id", "name"}, {"ProjectTickets.country.id", "ProjectTickets.country.name"}),
#"Expanded ProjectTickets.contact" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.country", "ProjectTickets.contact", {"id", "name"}, {"ProjectTickets.contact.id", "ProjectTickets.contact.name"}),
#"Expanded ProjectTickets.company" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.contact", "ProjectTickets.company", {"id", "identifier", "name"}, {"ProjectTickets.company.id", "ProjectTickets.company.identifier", "ProjectTickets.company.name"}),
#"Expanded ProjectTickets.site" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.company", "ProjectTickets.site", {"id", "name"}, {"ProjectTickets.site.id", "ProjectTickets.site.name"}),
#"Expanded ProjectTickets.board" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.site", "ProjectTickets.board", {"id", "name"}, {"ProjectTickets.board.id", "ProjectTickets.board.name"}),
#"Expanded ProjectTickets.status" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.board", "ProjectTickets.status", {"id", "name"}, {"ProjectTickets.status.id", "ProjectTickets.status.name"}),
#"Expanded ProjectTickets.project" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.status", "ProjectTickets.project", {"id", "name"}, {"ProjectTickets.project.id", "ProjectTickets.project.name"}),
#"Expanded ProjectTickets.phase" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.project", "ProjectTickets.phase", {"id", "name"}, {"ProjectTickets.phase.id", "ProjectTickets.phase.name"}),
#"Expanded ProjectTickets.department" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.phase", "ProjectTickets.department", {"id", "identifier", "name"}, {"ProjectTickets.department.id", "ProjectTickets.department.identifier", "ProjectTickets.department.name"}),
#"Expanded ProjectTickets.agreement" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.department", "ProjectTickets.agreement", {"id", "name"}, {"ProjectTickets.agreement.id", "ProjectTickets.agreement.name"}),
#"Expanded ProjectTickets.item" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.agreement", "ProjectTickets.item", {"id", "name"}, {"ProjectTickets.item.id", "ProjectTickets.item.name"}),
#"Expanded ProjectTickets.subType" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.item", "ProjectTickets.subType", {"id", "name"}, {"ProjectTickets.subType.id", "ProjectTickets.subType.name"}),
#"Expanded ProjectTickets.workType" = Table.ExpandRecordColumn(#"Expanded ProjectTickets.subType", "ProjectTickets.workType", {"id", "name"}, {"ProjectTickets.workType.id", "ProjectTickets.workType.name"}),
#"Removed fields not being used" = Table.RemoveColumns(#"Expanded ProjectTickets.workType",{"ProjectTickets.currency", "ProjectTickets.location", "ProjectTickets.approved", "ProjectTickets.customFields", "ProjectTickets.automaticEmailCc", "ProjectTickets.allowAllClientsPortalView", "ProjectTickets.customerUpdatedFlag", "ProjectTickets.automaticEmailContactFlag", "ProjectTickets.automaticEmailResourceFlag", "ProjectTickets.automaticEmailCcFlag", "ProjectTickets.source", "ProjectTickets.serviceLocation", "ProjectTickets.mobileGuid", "ProjectTickets.subBillingMethod", "ProjectTickets.billTime", "ProjectTickets.billExpenses", "ProjectTickets.billProducts", "ProjectTickets.contactPhoneNumber", "ProjectTickets.contactPhoneExtension", "ProjectTickets.wbsCode", "ProjectTickets.isIssueFlag", "ProjectTickets.predecessorType", "ProjectTickets.predecessorId", "ProjectTickets.predecessorClosedFlag", "ProjectTickets.lagDays", "ProjectTickets.lagNonworkingDaysFlag", "ProjectTickets.agreement.id", "ProjectTickets.agreement.name"}),
#"Changed Date Type Format" = Table.TransformColumnTypes(#"Removed fields not being used",{{"ProjectTickets.closedDate", type datetime}, {"ProjectTickets._info.dateEntered", type datetime}, {"ProjectTickets._info.lastUpdated", type datetime}}),
#"Added Remaining Hours" = Table.AddColumn(#"Changed Date Type Format", "Remaining Hours", each [ProjectTickets.budgetHours] - [ProjectTickets.actualHours]),
#"Replaced ^ null with 0" = Table.ReplaceValue(#"Added Remaining Hours",null,0,Replacer.ReplaceValue,{"Remaining Hours"}),
#"Changed All Hour Data Type" = Table.TransformColumnTypes(#"Replaced ^ null with 0",{{"Remaining Hours", type number}, {"ProjectTickets.budgetHours", type number}, {"ProjectTickets.actualHours", type number}}),
#"Added RemainingGreaterThanZero" = Table.AddColumn(#"Changed All Hour Data Type", "RemainingGreaterThanZero", each if [Remaining Hours] > 0 then [Remaining Hours] else 0),
#"Changed Type for ^" = Table.TransformColumnTypes(#"Added RemainingGreaterThanZero",{{"RemainingGreaterThanZero", type number}}),
#"Add TicketType Column" = Table.AddColumn(#"Added RemainingGreaterThanZero", "TicketType", each if Text.Contains([ProjectTickets.summary], "Programming") then "Programming" else if Text.Contains([ProjectTickets.summary], "Engineering") then "Engineering" else if Text.Contains([ProjectTickets.summary], "Remote") then "Integration Services" else if Text.Contains([ProjectTickets.summary], "Support") then "Support" else if Text.Contains([ProjectTickets.summary], "Project Management") then "Project Management" else if Text.Contains([ProjectTickets.summary], "Staging") then "Assembly" else if Text.Contains([ProjectTickets.summary], "Assembly") then "Assembly" else if Text.Contains([ProjectTickets.summary], "Installation") then "Installation" else if Text.Contains([ProjectTickets.summary], "Shipping") then "Warehouse" else if Text.Contains([ProjectTickets.summary], "Purchasing") then "Purchasing" else "Other")
in
#"Add TicketType Column"
