Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to up the data from Zendesk through PowerBI, but i only get the first results. How do i setup the pagination in Power Query to get all results? I've tried with List.Generate, but i'm unable to succeed so far. Do you got a solution?
The Query tells me what the next_page and last_page is.
The query is
let
Source = Json.Document(Web.Contents("https://xxxxxxxx.zendesk.com/api/v2/tickets.json?", [Headers=[Authorization="Basic ZGlXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"]])),
#"Converted to Table" = Table.FromRecords({Source})
in
#"Converted to Table"
Hi @Leggies
did you try to click on expand button?
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@v-xiaotang i've tried, but it only gives the first 100 results. I'm trying to make a loop, but nothing works so far.
Hey @Leggies ,
you have to loop through multiple API call.
Search in the community, that topic appeared many times. Also check the following video:
Pagination and DO/WHILE in Power BI / Power Query - YouTube
Hi @selimovd
I tried, but its not working. I get the error "We cannot convert a value of type List to type Number." This is the Query:
let
Page = {1,1000},
Source = Json.Document(Web.Contents("https://xxxxxxx.zendesk.com/api/v2/tickets.json?page="&Number.ToText(Page), [Headers=[Authorization="Basic ZGlxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])),
#"Converted to Table" = Table.FromRecords({Source}),
List = List.Generate(() =>
[Result= try GetData(1) otherwise null, Page = 1],
each [Result] <> null,
each [Result = try GetData([Page]+1) otherwise null, Page =[Page]+1],
each [Result])
in
#"Converted to Table"
@Leggies , do you need what is discussed here
https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78
Hi @amitchandak ,
I've tried your solution, but i get the following error:
Expression.Error: The field '@odata.count' of the record wasn't found.
Details:
tickets=[List]
next_page=https://xxxxxxx.zendesk.com/api/v2/tickets.json?page=2
previous_page=
count=9758
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |