Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Please see this article for how to avoid that error with relativepath.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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