Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Zenly
Regular 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 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

3 REPLIES 3
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


ppm1
Solution Sage
Solution 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

Microsoft Employee
Zenly
Regular 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? 

Zenly_0-1675067874689.png

 

Zenly_1-1675067922854.png

 

 

-Carl

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors