Need help on Loop for paginated API

[Zenly]

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

[mahoneypat]

Please see this article for how to avoid that error with relativepath.

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

Pat

[ppm1]

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]

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