Error on scheduled refresh: "The exception was raised by the IDbCommand interface."

[intelworks]

Hello everybody, I am running in the dreaded "The exception was raised by the IDbCommand interface" error when Scheduled Refresh kicks in, in a public report I am working on.

I pinned down the problem to a specific query but I cannot find a solution yet.

I have 5 sources, 3 from raw GitHub data (https://raw.github.....), one from a local file connected through Personal Gateway running on my computer and the last (the culprit) from a public Google spreadsheet. All of these sources privacy settings are set to Public with Anonymous credentials.

The report refreshes fine on PBI Desktop and when I publish it, but it fails on Scheduled or On Demand refresh from the Power BI Service. The complete error I get is:

We're sorry, an error occurred during evaluation.;The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified. The exception was raised by the IDbCommand interface. Table: problematic_table

The full query is:

let
    Source = Web.BrowserContents(
        "https://docs.google.com/spreadsheets/d/1EPoJ_T221tEy0K5QTkaZKjA-lHo9VAEr6rFPJ_Qdtds/htmlview", 
        [WaitFor = [Timeout = #duration(0, 0, 0, 2)]]
    ),
    #"Extracted Table From Html" = Html.Table(
        Source, 
        {
            {
                "Column1", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(1), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(1)"
            }, 
            {
                "Column2", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(2), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(2)"
            }, 
            {
                "Column3", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(3), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(3)"
            }, 
            {
                "Column4", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(4), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(4)"
            }, 
            {
                "Column5", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(5), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(5)"
            }, 
            {
                "Column6", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(6), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(6)"
            }, 
            {
                "Column7", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(7), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(7)"
            }, 
            {
                "Column8", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(8), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(8)"
            }, 
            {
                "Column9", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(9), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(9)"
            }, 
            {
                "Column10", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(10), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(10)"
            }, 
            {
                "Column11", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(11), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(11)"
            }, 
            {
                "Column12", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(12), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(12)"
            }, 
            {
                "Column13", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(13), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(13)"
            }, 
            {
                "Column14", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(14), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(14)"
            }, 
            {
                "Column15", 
                "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR > :nth-child(15), DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR > :nth-child(15)"
            }
        }, 
        [RowSelector
            = "DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > TR, DIV[id='0'] > DIV.ritz.grid-container:nth-child(1) > TABLE.waffle:nth-child(1) > * > TR"]
    ),
    #"Removed Top Rows" = Table.Skip(#"Extracted Table From Html", 1),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Removed Top Rows", 
        {{"Column1", Int64.Type}}
    ),
    #"Filtered Rows" = Table.SelectRows(
        #"Changed Type", 
        each [Column1] <= 22
    ),
    #"Promoted Headers" = Table.PromoteHeaders(
        #"Filtered Rows", 
        [PromoteAllScalars = true]
    ),
    #"Renamed Columns" = Table.RenameColumns(
        #"Promoted Headers", 
        {
            {"posti letto TI (2018)", "2019"}, 
            {"posti letto TI (ultimo)", "2020"}
        }
    ),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Renamed Columns", 
        {"Regione", "2019", "2020"}
    ),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(
        #"Removed Other Columns", 
        {"Regione"}, 
        "Anno", 
        "Totale posti letto"
    ),
    #"Renamed Columns1" = Table.RenameColumns(
        #"Unpivoted Columns", 
        {{"Regione", "Area"}}
    ),
    #"Added Conditional Column" = Table.AddColumn(
        #"Renamed Columns1", 
        "Custom", 
        each if [Area] = "Abruzzo" then 13 
        else if [Area] = "Basilicata" then 17
        else if [Area] = "Bolzano" then 4
        else if [Area] = "Calabria" then 18
        else if [Area] = "Campania" then 15
        else if [Area] = "Emilia-Romagna" then 8
        else if [Area] = "Friuli-Venezia Giulia" then 6
        else if [Area] = "Lazio" then 12
        else if [Area] = "Liguria" then 7
        else if [Area] = "Lombardia" then 3
        else if [Area] = "Marche" then 11
        else if [Area] = "Molise" then 14
        else if [Area] = "Piemonte" then 1
        else if [Area] = "Puglia" then 16
        else if [Area] = "Sardegna" then 20
        else if [Area] = "Sicilia" then 19
        else if [Area] = "Toscana" then 9
        else if [Area] = "Trento" then 4
        else if [Area] = "Umbria" then 10
        else if [Area] = "Valle d'Aosta" then 2
        else if [Area] = "Veneto" then 5
        else null
    ),
    #"Renamed Columns2" = Table.RenameColumns(
        #"Added Conditional Column", 
        {{"Custom", "Codice Regione"}}
    ),
    #"Changed Type1" = Table.TransformColumnTypes(
        #"Renamed Columns2", 
        {
            {"Anno", Int64.Type}, 
            {"Codice Regione", type text}, 
            {"Totale posti letto", Int64.Type}, 
            {"Area", type text}
        }
    ),
    #"Sorted Rows" = Table.Sort(
        #"Changed Type1", 
        {{"Anno", Order.Ascending}}
    )
in
    #"Sorted Rows"

As you can see the first step is pretty complicated, but it got generated by Power BI when I picked the WebBrowser.Contents suggested table.

As soon as I delete this query, the report works perfectly with Scheduled Refresh and On Demand refresh in Power BI Service. On the contrary, as soon as I put it back in, I start seeing the error I described.

Has anyone run into a problem like this? I am sensing it could have something to do with that extremely intricated WebBrowser.Contents pattern, possibly because it is a very looong string inside the query. But aside from this I have no other ideas.

Please let me know if you can help me out on this, it would spare me from updating the report manually every day.

Thanks in advance,

Intelworks

[nandukrishnavs]

@intelworks 

If the above solution not working, Try to setup a gateway to schedule the dataset refresh.

https://docs.microsoft.com/en-us/power-bi/refresh-troubleshooting-refresh-scenarios#refresh-using-we...

https://docs.microsoft.com/en-us/power-bi/service-gateway-data-sources

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[intelworks]

Thanks to @nandukrishnavs and @MFelix , but the fixes you are proposing are not related to my problem.

The error is "The exception was raised by the IDbCommand interface" and it is not related to the Personal Gateway (that connection works flawlessly). Also, the function I am using is not Web.Page, but WebBrowser.Contents with no query parameters, and again the error is not related to the ability to enable Scheduled Refresh but it happens when the Scheduled Refresh kicks in (also waiting time is high, it usually fails around 15-20 minutes after the Scheduled/On Demand Refresh start).

I stated clearly that as soon as I remove that specific query the report refreshes automatically and on demand in the Power BI Service while, as soon as I put it back in, it gives me this super weird error

We're sorry, an error occurred during evaluation.;The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified The system cannot find the file specified. The exception was raised by the IDbCommand interface. Table: problematic_table

Please note the repetition of the error "The system cannot find the file specified".

To be sure, I created a separate report only containing the problematic query and enabled Scheduled Refresh in the Service, and it still fails in the same way (with identical Data Source settings, Anonymous credential and Public privacy level). You can copy paste my query and obtain the very same result indipendently if you want to troubleshoot the issue (you will also able to see that you cannot even use the Personal Gateway in this scenario as stated in the Power BI Service)

Thanks again for the interest.

[nandukrishnavs · Accepted solution]

@intelworks can you install the enterprise gateway and add the data source under "manage gateway"? https://docs.microsoft.com/en-us/power-bi/service-gateway-data-sources

[intelworks]

@nandukrishnavs thanks for the help, refreshing this cloud connection through the Enterprise Gateway now works.

I have never used the Enterprise Gateway so I never tested it, I always used the Personal Gateway.

Can you tell me why it's working now and why it didn't work before? I am puzzled.

Anyway, thanks again, you solved the problem.

[MFelix]

Hi  @intelworks ,

Check if this blog post can help you get around your issue:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...