Forum Discussion
Zenly
3 years agoRegular Visitor
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 re...
ppm1
3 years agoSolution Sage
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
3 years agoRegular Visitor
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