Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"
Solved! Go to Solution.
I found a blog online that shows me how to do it but thank you!
Here is another to do this you can consider.
Power BI - Tales From The Front - REST APIs - YouTube
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @rachaelwalker ,
I found some articles which might be helpful to solve your issue:
How To Do Pagination In Power Query
Iterate Over Dynamic Web API Pages With Power Query – How To Resolve Cursor-Based Pagination
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the resources! I created a query following their instructions but receiving the following error
Expression.Error: We cannot apply field access to the type List.
Details:
Value=[List]
Key=@odata.count
This is my m code. Any ideas?
I found a blog online that shows me how to do it but thank you!