Forum Discussion

HugoAPereira's avatar
HugoAPereira
Helper I
5 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 again?          let Source = Json.Document(Web.Contents("https://api...
Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    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"
lbendlin's avatar
lbendlin
Super User
5 years ago

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. 

HugoAPereira's avatar
HugoAPereira
Helper I
5 years ago
Hi, What data do you need to help me?
  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    One (or all) of these:

    - the API's documentation

    - the output of the first call

    - a sample API key

    • lbendlin's avatar
      lbendlin
      Super User
      5 years ago

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

        Is perfect! It worked the first time, it has no errors and it was extraordinary support. Thank you very much.