topic Re: REST API Paging Power Query in Developer

REST API Paging Power Query

HugoAPereira — Tue, 06 Oct 2020 10:58:23 GMT

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

Re: REST API Paging Power Query

lbendlin — Tue, 06 Oct 2020 21:58:30 GMT

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.

Re: REST API Paging Power Query

HugoAPereira — Wed, 07 Oct 2020 08:20:22 GMT

Hi, What data do you need to help me?

Re: REST API Paging Power Query

lbendlin — Wed, 07 Oct 2020 11:07:17 GMT

One (or all) of these:

- the API's documentation

- the output of the first call

- a sample API key

Re: REST API Paging Power Query

lbendlin — Wed, 07 Oct 2020 14:58:35 GMT

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"

Re: REST API Paging Power Query

HugoAPereira — Wed, 07 Oct 2020 15:51:46 GMT

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

Re: REST API Paging Power Query

HugoAPereira — Tue, 20 Oct 2020 23:57:03 GMT

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

Re: REST API Paging Power Query

lbendlin — Wed, 21 Oct 2020 11:24:47 GMT

As always Chris Webb has the answer:

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