Forum Discussion

HugoAPereira's avatar
HugoAPereira
Helper I
6 years ago
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?  let Source = Json.Document(Web.Contents(url, [Headers=[Authorization="x...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    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's avatar
    ImkeF
    6 years ago

    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

     

  • ImkeF's avatar
    ImkeF
    6 years ago

    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's avatar
HugoAPereira
Helper I
6 years ago
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"
ImkeF's avatar
ImkeF
Community Champion
6 years ago

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