Forum Discussion
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?
let
Source = Json.Document(Web.Contents(url, [Headers=[Authorization="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])),
iterations = Source[Total_Pages], // get the information within the response
url = "https://api.shopk.it/v1/order/?X-API-KEY=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", // here goes your URL
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url, [Headers=[Authorization="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])),
data = try Source[connections] otherwise null, //get the data of the first page
next = try Source[next_page_url] otherwise null, // the script ask if there is another page
res = [Data=data, Next=next]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Data]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data]),
#"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converti en table"
oops - I think it should be this:
let Source = Json.Document(Web.Contents("https://api.shopk.it/v1/order/?X-API-KEY=<token>")), ToTable = Record.ToTable(Source), // Result = Table.RemoveLastN(ToTable,1), NextPage = Source[paging][next], Custom1 = List.Generate( () => [ResultRecord = Source, NextPage = NextPage], each [ResultRecord][paging][next] <> null, each [ ResultRecord = Json.Document(Web.Contents([NextPage])), Result = Table.RemoveLastN(Record.ToTable(ResultRecord),1), NextPage = ResultRecord[paging][next] ] // ,each [Result] ) in Custom1Hi HugoAPereira ,
pleased to see 🙂
You simply have to append the first Result to what the List.Generate returns like so:
let Source = Json.Document(Web.Contents("https://api.shopk.it/v1/order/?X-API-KEY=<token>")), ToTable = Record.ToTable(Source), // Result = Table.RemoveLastN(ToTable,1), NextPage = Source[paging][next], Custom1 = List.Generate( () => [ResultRecord = Source, NextPage = NextPage], each [ResultRecord][paging][next] <> null, each [ ResultRecord = Json.Document(Web.Contents([NextPage])), Result = Table.RemoveLastN(Record.ToTable(ResultRecord),1), NextPage = ResultRecord[paging][next] ] // ,each [Result] ), Append = Result & Custom1 in AppendHi HugoAPereira
try this:
let Source = Json.Document(Web.Contents("https://api.shopk.it/v1/order/?X-API-KEY=<token>")), 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] ), Append = Result & Custom1 in Append
19 Replies
if change to iterations = Source[paging], the error is:
Hi HugoAPereira
these errors are hard to debug without seeing what's actually going on.
But please do the following:
- Create a new query where you manually expand the data in the first 2 levels (so that I can see how the page information will be used to get the next item)
- Open the advanced editor, copy all the code
- Paste the code into a text editor and replace the key and other confidential information
- Paste that cleaned code here
Hi,
Simple query
let Source = Json.Document(Web.Contents("https://api.shopk.it/v1/order/?X-API-KEY=<token>")), #"Converted to Table" = Record.ToTable(Source) in #"Converted to Table"Result:
