Forum Discussion
Solved
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.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
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"
7 Replies
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.
- Hi, What data do you need to help me?
One (or all) of these:
- the API's documentation
- the output of the first call
- a sample API key
