locked
Power Query "Unexpected error: Evaluation was canceled." Error :-( RRS feed

  • Question

  • The last post I can find on this error is from January 2019.  In that post, it was suggested to disable background refresh, which I have done, and then basically "wait for a new version".  This was a year ago...

    Today, everything was rocking along just fine until this week I started getting the subject line errors in my queries.  I have 19 "Connection Only" queries, which are appended or merged into 4 final data loads (fact tables) and 19 data loads for dimension (lookup) tables.

    I am running 64-bit version of MS Office 2013 w/ 16g of memory.  My version of PQ is 2.62.5222.701 (64-bit) and my version of Excel is 15.0.5189.1000 64-bit.

    This error is bringing my whole data model down.  It is not a "cosmetic" error that has no affect on the data the refresh stops and says "there is an error in xyz query, do you want to continue with other queries".  If you continue, the error is persistent, stopping loads for every query that is affected.  

    I can click through each step and / or click the "retry" button in power query editor and there is no error and it seems to be fixed until the next refresh.  This has consumed my whole vacation getting ready for Monday's upcoming reports. 

    Any suggestions on the following would be helpful.

    1. What causes this error? 
    2. How do I fix it?

    Is it low memory?  w/ 16 gb of memory I have run data models with 9M records.  This data model while "wide" (31 columns) only has 200k records.  It should not be a memory issue.  Is it the number of related dimension tables?  Should I flatten my tables and incorporate the dimensions in the fact table?  (This will create other issues when relating the fact tables however, so I would prefer to not do this.) 

    Is my PQ version out of data and a fix was published?  Or an updated version of Excel? (My IT Department holds the key to this, so I may not be able to do much with updating patches to Excel.  New version such as 2016 is out of the question at this point.)

    Could it be an error in the data somewhere and PQ just can't communicate it any other way?  This error is so general in nature, it's hard to know where to start looking...

    Something else?

    Thanks in advance...

    John


    John Thomas

    Wednesday, January 1, 2020 7:32 PM

Answers

  • Hi John. Did you by any chance disable background analysis on your queries? If so, you should only see this error in the "interactive" parts of PQ (such as the Query Editor or in flyouts that provide table previews). It sounds like you're seeing it during load/refresh, however, which shouldn't be the case.

    Ehren

    • Marked as answer by jbt_PwrPvt Tuesday, January 14, 2020 9:17 PM
    Monday, January 13, 2020 10:49 PM

All replies

  • Hi John. Did you by any chance disable background analysis on your queries? If so, you should only see this error in the "interactive" parts of PQ (such as the Query Editor or in flyouts that provide table previews). It sounds like you're seeing it during load/refresh, however, which shouldn't be the case.

    Ehren

    • Marked as answer by jbt_PwrPvt Tuesday, January 14, 2020 9:17 PM
    Monday, January 13, 2020 10:49 PM
  • Thanks Ehren.  In spite of what I said in my post, I also found an error in the data.  The frustrating part is that the error in question was masking the true error, so it's very difficult to trouble shoot the problem.  These still show up from time to time now, but not as big an issue as it was before.

    John Thomas

    Tuesday, January 14, 2020 9:18 PM
  • Hi John. If you can share a simple repro (with any sensitive data removed), this will help us track down why this is happening. An error in your data shouldn't cause the evaluation to be canceled during refresh.

    Ehren


    Tuesday, January 14, 2020 9:39 PM