How to setup the pagination next_page for all pages

[Leggies]

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"

[v-xiaotang]

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.

[Leggies]

@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.

[selimovd]

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

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Leggies]

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"

[amitchandak]

@Leggies , do you need what is discussed here

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

[Leggies]

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