none
How to stop showing the Datasource.Error Popup

    Question

  • Hello all

    I have an Excel file with a couple of Power Queries downloading data every minute from different sources.

    Every once in a while the source/server has some connection problems. That is expected.

    But when that happens, an annoying popup appears and it stops the file for doing anything else until "OK" is clicked.

    "The Remote name could not be resolved" or "Unable to connect to the remote server"

    https://i.imgur.com/OivvtbN.png

    We need to be sure that the file runs non-stop 24/7 without interruptions. So how can we stop this popup from appearing?

    I've tried with the following VBA, but still not working.

    Sub Optimise()
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    ActiveSheet.DisplayPageBreaks = False
    changeflag = 0
    On Error GoTo 0
    End Sub

    Here's the sample file: http://www.mediafire.com/file/9tuc6tiap72trrq

    The query downloads data from an API every 1M, so to reproduce the error, just disconnect internet and wait a minute or open it while disconnected.

    • Edited by NicoPer Thursday, June 14, 2018 12:32 PM
    Tuesday, June 12, 2018 1:05 PM

Answers

  • Hey,

    The 3 pieces of code are basically each a query on their own as it was easier and more efficient for me to work and look at the scenario this way.

    Here's the workbook that I used.

    I'm not sure if the popup can be disabled or not. What this code does is that whenever you get an error when hitting the "https://api.binance.com/api/v1/ticker/allPrices" API Endpoint, that it'll try to hit that endpoint again every 20 seconds until it finally gets some data out of it.

    AFAIK there's no way to disable the popup and the way that Power Query works is that whenever you hit that "Refresh" button, PQ will try to completely get rid of the cached table and replace it with a new data. If the refresh process errors out then it'll give you a pop up and a reason as to why the error happened and you'll have to click on the OK button in order to maintain the previous version of your data.

    You could use VBA and PQ to create some sort of validation workflow, but I have no knowledge of VBA so I wouldn't be able to give you an exact way to do this and, on top of my head, I don't think it would be an infallible solution either. The retry function that I provided is probably your best bet in this case.

    • Marked as answer by NicoPer Monday, June 18, 2018 1:36 PM
    • Unmarked as answer by NicoPer Monday, June 18, 2018 1:44 PM
    • Marked as answer by NicoPer Monday, June 18, 2018 6:05 PM
    Monday, June 18, 2018 3:14 AM

All replies

  • Anyone?
    Thursday, June 14, 2018 12:34 PM
  • why not add a somewhat of a loop in your M code to handle this? I don't think that you can write some VBA code to get past that "OK" click (hopefully I'm wrong)

    Basically a logic that does something like: "if Source step has an error, then retry every minute until it doesn't return an error"

    Probably a good place to try a recursive RETRY function.

    Thursday, June 14, 2018 3:46 PM
  • Thank you Miguel very much for your reply.

    Can you please show me how?


    • Edited by NicoPer Friday, June 15, 2018 12:57 PM
    Thursday, June 14, 2018 7:14 PM
  • Thank you Miguel very much for your reply.

    Can you please show me how?


    I was hoping that someone would jump in and show a way to get past the OK button through VBA.

    Here's how you can accommodate your code to keep doing a retry until it fetches your data. 

    CAUTION: this could create an endless loop if you can't connect to the data or you get an error like unauthorized access. Use it at your own risk.

    fxRetry = (MyCall as function) =>
    let
    Buffered = Binary.Buffer (MyCall() ),
    Output = if Record.Field(try Buffered , "HasError") = false then 
    Buffered else 
    Function.InvokeAfter(()=>@fxRetry(MyCall), #duration(0,0,0,20) )
    in 
    Output

    Call = ()=> Web.Contents("https://api.binance.com/api/v1/ticker/allPrices")

    Output = let
        Origen = fxRetry(Call),
        Personalizado1 = Json.Document( Origen),
        Personalizado2 = Table.FromRecords( Personalizado1 ),
        #"Tipo cambiado" = Table.TransformColumnTypes(Personalizado2,{{"symbol", type text}, {"price", type number}})
    in
        #"Tipo cambiado"


    • Edited by Miguel Escobar Sunday, June 17, 2018 10:18 PM adding the caution
    Sunday, June 17, 2018 10:18 PM
  • Gracias Miguel :)

    Those are 3 snippets of code, do they go all together?

    I thought I pasted the query here, but I didn't. This is the original query:

    let
        Source = Json.Document(Web.Contents("https://api.binance.com/api/v1/ticker/allPrices")),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.List),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"symbol", "price"}, {"Column1.symbol", "Column1.price"}),
        #"Expanded Column2" = Table.ExpandListColumn(#"Expanded Column1", "Column2"),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Column2",{"Column2"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.symbol", "Symbol"}, {"Column1.price", "Price"}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Price", type number}})
    
    in
        #"Changed Type"

    How do I integrate your code?

    This just disables the popup right? or does it recall the query?
    I'm asking because the query refresh is managed from the data connections every 1 min.
    No need to recall, just don't show the popup.
    Like, if error, do nothing.



    • Edited by NicoPer Sunday, June 17, 2018 10:43 PM
    Sunday, June 17, 2018 10:37 PM
  • Hey,

    The 3 pieces of code are basically each a query on their own as it was easier and more efficient for me to work and look at the scenario this way.

    Here's the workbook that I used.

    I'm not sure if the popup can be disabled or not. What this code does is that whenever you get an error when hitting the "https://api.binance.com/api/v1/ticker/allPrices" API Endpoint, that it'll try to hit that endpoint again every 20 seconds until it finally gets some data out of it.

    AFAIK there's no way to disable the popup and the way that Power Query works is that whenever you hit that "Refresh" button, PQ will try to completely get rid of the cached table and replace it with a new data. If the refresh process errors out then it'll give you a pop up and a reason as to why the error happened and you'll have to click on the OK button in order to maintain the previous version of your data.

    You could use VBA and PQ to create some sort of validation workflow, but I have no knowledge of VBA so I wouldn't be able to give you an exact way to do this and, on top of my head, I don't think it would be an infallible solution either. The retry function that I provided is probably your best bet in this case.

    • Marked as answer by NicoPer Monday, June 18, 2018 1:36 PM
    • Unmarked as answer by NicoPer Monday, June 18, 2018 1:44 PM
    • Marked as answer by NicoPer Monday, June 18, 2018 6:05 PM
    Monday, June 18, 2018 3:14 AM
  • Thanks Miguel. I've tried it, but having some problems.

    Opened it, then disconnected from the internet and the popup didn't show up.

    Waited some time and connected to the internet again, and it seemed to start refreshing again.

    I thought it worked, but when I check the prices, I see that they are not being updated.
    They are static with the same prices they had before the disconnect. You can check it yourself.

    Can you spot the error? How can we solve it?


    • Edited by NicoPer Monday, June 18, 2018 1:53 PM
    Monday, June 18, 2018 1:39 PM
  • Hey Nico,

    Could it be that you're looking at a different sheet? There are a few in that workbook and one of them contains the old numbers that your original file had - which is just a static table.

    The correct worksheet name that contains the Output query should be "Hoja3", please take a look at it. 

    I'm unable to replicate the issue that you're describing.

    Monday, June 18, 2018 3:05 PM
  • Ha. You're right, was looking at the wrong sheet. I'm very sorry about that!

    It's working like a charm.. thanks a million for your code an patience. I appreciate it.
    • Edited by NicoPer Monday, June 18, 2018 6:12 PM
    Monday, June 18, 2018 6:10 PM