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? 

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"

[ImkeF · Accepted solution]

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
    Custom1

[ImkeF · Accepted solution]

Hi @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
    Append

[HugoAPereira]

if change to iterations = Source[paging], the error is:

[ImkeF]

Hi @HugoAPereira 

these errors are hard to debug without seeing what's actually going on.

But please do the following:

1. 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)
2. Open the advanced editor, copy all the code 
3. Paste the code into a text editor and replace the key and other confidential information
4. Paste that cleaned code here

[HugoAPereira]

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:

[ImkeF]

Hi @HugoAPereira 

please re-read my previous answer.

I need the steps on how to use the paging - so please extract the data one level further.

[HugoAPereira]

let
    Source = Json.Document(Web.Contents("https://api.shopk.it/v1/order/?X-API-KEY=<token>")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"id", "hash", "total", "subtotal", "product_tax", "total_tax", "discount", "shipping", "coupon_code", "created_at", "update_at", "sent_at", "paid_at", "currency", "status", "status_alias", "status_description", "paid", "is_new", "invoice_url", "weight", "observations", "note", "client_note", "custom_field", "tracking_code", "tracking_url", "shipping_url", "coupon", "payment", "shipment_method", "permalink", "client", "products"}, {"Value.id", "Value.hash", "Value.total", "Value.subtotal", "Value.product_tax", "Value.total_tax", "Value.discount", "Value.shipping", "Value.coupon_code", "Value.created_at", "Value.update_at", "Value.sent_at", "Value.paid_at", "Value.currency", "Value.status", "Value.status_alias", "Value.status_description", "Value.paid", "Value.is_new", "Value.invoice_url", "Value.weight", "Value.observations", "Value.note", "Value.client_note", "Value.custom_field", "Value.tracking_code", "Value.tracking_url", "Value.shipping_url", "Value.coupon", "Value.payment", "Value.shipment_method", "Value.permalink", "Value.client", "Value.products"})
in
    #"Expanded Value"

[HugoAPereira]

Good afternoon. Thanks in advance for your help @ImkeF. Below is an image of the result. I think we're almost done.

[ImkeF]

Oh yes, my bad - that sneaked in last minute...

Please try the following code instead:

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 = [Result][paging][next]
        ]
   //    ,each [Result]
    )
in
    Custom1

[HugoAPereira]

Is almost. Thank you @ImkeF

[ImkeF]

Hm... please use this query to debug:

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 = [Result][paging][next]
        ]
   //    ,each [Result]
    )
{1}
in
    Custom1

[HugoAPereira]

follow the images. Thank you @ImkeF!

[ImkeF]

Yes, that's the problem with coding without data... 😉

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
    Custom1

[ImkeF · Accepted solution]

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
    Custom1

[ImkeF · Accepted solution]

Hi @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
    Append

[HugoAPereira]

hello @ImkeF . Hope you're well.

I found out now that the query is not returning data from the last page (where next= null). What will it be?

[ImkeF · Accepted solution]

Hi @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

[HugoAPereira]

Is perfect! Thank you very much for your help, you are without a doubt an expert!

[HugoAPereira]

Hello! I just got to see the post today. I want to thank you for your help, which was impeccable! The query is working very well! Thank you @ImkeF 

[HugoAPereira]

It's almost prefect. Just let page 2 also have records. Thank you very very much @ImkeF!

[ImkeF]

Hi @HugoAPereira ,

please 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 [ResultRecord][paging][next] <> null,
        each [
            ResultRecord = Json.Document(Web.Contents([NextPage])),
            Result = Table.RemoveLastN(Record.ToTable(ResultRecord),1),
            NextPage = [Result][paging][next]
        ]
       ,each [Result]
    )
in
    Custom1