Forum Discussion
HugoAPereira
5 years agoHelper I
REST API Paging Power Query
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...
- 5 years ago
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"
lbendlin
5 years agoSuper User
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
5 years agoHelper I
Is perfect! It worked the first time, it has no errors and it was extraordinary support. Thank you very much.