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...
  • 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"