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

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

 

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

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

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

        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.