REST API Paging Power Query

[HugoAPereira]

Good afternoon. I need help to list all the data on my website and that they have several pages.
@ImkeF can you help me again? 

let
    Source = Json.Document(Web.Contents("https://api.weasy.io/v1/catalog?X-API-KEY=<key>")),
    ToTable = Record.ToTable(Source), //
    Result = Table.RemoveLastN(ToTable,1),
    NextPage = Source[paging][next],
    Custom1 = List.Generate( () =>
        [ResultRecord = Source, NextPage = NextPage],
        each [NextPage] <> null,
        each [
            NextPage = [ResultRecord][paging][next],
            ResultRecord = Json.Document(Web.Contents(NextPage)),
            Result = Table.RemoveLastN(Record.ToTable(ResultRecord),1)
 
        ]
   //    ,each [Result]
    ),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Result"}, {"Result"}),
    #"Expanded Result" = Table.ExpandTableColumn(#"Expanded Column1", "Result", {"Name", "Value"}, {"Name", "Value"}),
    Append = Result & #"Expanded Result",
    #"Filtered Rows" = Table.SelectRows(Append, each ([Name] <> null))
in
    #"Filtered Rows"

1234567

[lbendlin]

This line 

   Result = Table.RemoveLastN(ToTable,1),

is not used subsequently. Is that intentional?

Without details on how your API's pagination works it is hard to give you advice. 

[HugoAPereira]

Hi, What data do you need to help me?

[lbendlin]

One (or all) of these:

- the API's documentation

- the output of the first call

- a sample API key

[lbendlin · Accepted solution]

Based on the information you provided here is one possible implementation.  It is not optimized (page 1 is fetched twice) but it works.

You need two parameters - APIKey  and Limit .  The Limit parameter allows you to control how many items you want to retrieve in each web call.

let
    Source = Json.Document(Web.Contents("https://api.weasy.io/v1/catalog?X-API-KEY=" & APIKey & "&limit=" & Limit)),
    pages = {1..Source[paging][totalpages]},
    #"Converted to Table" = Table.FromList(pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Data", each Json.Document(Web.Contents("https://api.weasy.io/v1/catalog?X-API-KEY=" & APIKey & "&limit=" & Limit & "&page=" & [Column1]))),
    #"Expanded Data" = Table.ExpandRecordColumn(#"Added Custom", "Data", {"items"}, {"items"}),
    #"Expanded items" = Table.ExpandListColumn(#"Expanded Data", "items")
in
    #"Expanded items"

[HugoAPereira]

Hi @lbendlin ,

I need your help again.
I already have more than 5000 products on the site. The problem is that when obtaining the data, the error message "(429): Too Many Requests" is returned. The site only allows 100 requests per minute. How do I get around this problem?

thank you

[lbendlin]

As always Chris Webb has the answer:

https://blog.crossjoin.co.uk/2015/04/30/using-function-invokeafter-in-power-query/

[HugoAPereira]

Is perfect! It worked the first time, it has no errors and it was extraordinary support. Thank you very much.