Forum Discussion

Zenly's avatar
Zenly
Regular Visitor
3 years ago

Need help on Loop for paginated API

Hi,

 

I wish to create a loop code for the following URL https://boardgamegeek.com/xmlapi2/plays?username=ZENLY&mindate=2018-01-01&page=1

 

The https://boardgamegeek.com/xmlapi2/plays API only returns 100 results per page, and I would like to automate the Power BI Query to get data for all the pages and stop when the API/webpage don't find/returns any more data.

 

So for 940 results, the loop should get data for 10 XML pages/tables, append these into one big table, and then stop.

 

Thanks for any replies. 🙂

-Carl

Need Help

3 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    This is definitely doable. I don't have time to work it out, but here is a quick way to get going, but creating a list of page numbers and concatenating them into the web call on each row. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

     

    let
    Source = {1..10},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "PageNum"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Xml.Tables(Web.Contents("https://boardgamegeek.com/xmlapi2/plays?username=ZENLY&mindate=2018-01-01&page="&[PageNum]))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"play"}, {"play"})
in
    #"Expanded Custom"

     

    Pat

    • Zenly's avatar
      Zenly
      Regular Visitor
      3 years ago

      Hi Pat,

       

      Thank you for your reply. I tried your code and it is very helpfull. Will this code break the Power BI Pro Refresh? 

       

       

       

      -Carl