Trying to make a loop to get all API data (Pagination)

[Leggies]

Hi,
I succeed to get some data in Power Query though api call, but not get all data.

Now I'm trying to create a loop to get all the API data from Zendesk, 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"

Can somebody tell me what is wrong with the code i'm using? I tried a lot of methods, but with no success.

[Icey · Accepted solution]

Hi @Leggies ,

Please create a new table with all page values and then refer to this blog to use column as parameter to return all needed values:

Use Column as Parameter for Custom Function in Pow... - Microsoft Power BI Community.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Leggies , For relative path, refer try this

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...