List.Generate - Connect to REST API without Total Number of Pages and Total Number of Entries

[Anonymous]

Hello Guys,

I'm trying to get data from a REST API that doesn't have total number of pages or total number of entries.

I have search on internet for answer but none fit for my challenge, so I came here to get sugestions.

So, the first part I got it. I made a funciont name "GetPageClientes",

= (page as text) =>
let
Source = try Json.Document(Web.Contents("https://api.mysite.io/v2/customers?page=" & page & "&limit=1000", [Headers=[Authorization="Bearer NTg***********"]])) otherwise [customers=null],
#"Converted to Table" = Table.FromRecords({Source})
in
#"Converted to Table"

and after some tentatives I got the total number of pages, in the case, 8 pages.

In the sequence, I looked for List.Generate

= List.Generate(() => [result = try GetPageClientes("1") otherwise null, n=1],
each [result]<>null,
each [result = try GetPageClientes([n] + 1) otherwise null, n=[n]+1],
each [result])

to list everypage together, but It's not easy for me and not work as intended as well.

So, if anyone could help, I will appreciate.

--

Thank all.

Alexandre.

[Anonymous · Accepted solution]

I got it... i got it... i'm sooooooo happy.

= List.Generate(
() => [PAGE = 1, RESULTS = GetPageClientes("1")],
each Table.RowCount(Table.SelectRows([RESULTS], each [count]>0)) >0,
each [PAGE = [PAGE] + 1, RESULTS = GetPageClientes(Number.ToText([PAGE] + 1))],
each [RESULTS])

My research was that:

https://www.dalesandro.net/handling-paginated-rest-api-results-with-power-query/

https://community.powerbi.com/t5/Power-Query/List-generation-not-stopping-pagination-without-knowing...

https://www.thebiccountant.com/2020/05/15/miss-last-page-paging-power-bi-power-query/

https://www.youtube.com/watch?v=05yhwnuCjRw

https://www.youtube.com/watch?v=IASH1Fr16HY

[ImkeF]

Hi @Anonymous ,
that really is an annoying behaviour. Please check out this blogpost where I've described a workaraound: How not to miss the last page when paging with Power BI and Power Query (thebiccountant.com)

[Anonymous]

I love you @ImkeF . I'm going to check.

Many tks,

--

Alexandre.

[Anonymous]

hello everyone,

I get partial success now. I can list 7 pages, but I know that there is a 8 one.

Probably, the solution is on the line 4... if someone could suggest anything, I will appreciate.

Tks in advance,

--

Alexandre.