Web API Query Issue

[Zenly]

Hi,

This is my first post on this forum. My name is Carl. 🙂

I wish to import the XML table https://boardgamegeek.com/xmlapi2/collection?username=Zenly&excludesubtype=boardgameexpansion  into Power BI. I pasted the URL above in Power BI Desktop - Transform Data- New Source - Web - URL, and get the following response:

After manually refreshing I get the XML table but there seem to be an underlaying error:

When I try to expand the table I get the following error:

I have noticed that sometimes the XML tables from https://boardgamegeek.com/xmlapi2/  gives the "Your request for this collection has been accepted and will be processed. Please try again later for access" message, and sometimes it gives the XML table on first try. My problem is that my Power BI Query breaks once I get the Expression Error above.

Can someone please help me with a code that will bypass this problem? 

Thanks for any replies. 🙂

-Carl

[Daryl-Lynch-Bzy]

Hi @Zenly - sorry, but I sent an advanced developer feature that is not available in normal Power Query, and I had trouble replicating your issue.  The API documentation suggests there are throttling limits and process latency.  The API will issue a valid "202" response with the message "Your request etc... ".  The function should allow Power Query to wait for a "200" response, which contains the XML file.  The "Value.WaitFor" is not an Power Query function, so it is not recognising as a custom function.  I am not sure how to fix because I can't replicate the "202" response. 

[Daryl-Lynch-Bzy]

Hi @Zenly - you should consider Using the wait-retry pattern in Power Query connectors - Power Query | Microsoft Learn.  

[Zenly]

Hi @Daryl-Lynch-Bzy: Thank you for your reply. My coding and syntax skills are very limited. How would the code for https://boardgamegeek.com/xmlapi2/collection?username=Zenly&stats=1 with wait-retry pattern look like? 

let
    Source = Xml.Tables(Web.Contents("https://boardgamegeek.com/xmlapi2/collection?username=Zenly&stats=1")),

    waitForResult = Value.WaitFor(
        (iteration) =>
            let
                result = Web.Contents(url, [ManualStatusHandling = {500}, IsRetry = iteration > 0]),
                status = Value.Metadata(result)[Response.Status],
                actualResult = if status = 500 then null else result
            in
                actualResult,
        (iteration) => #duration(0, 0, 0, Number.Power(2, iteration)),
        5)
in
    if waitForResult = null then
        error "Value.WaitFor() Failed after multiple retry attempts"
    else
        waitForResult