Two common PowerPivot error messages

Been meaning to post this for awhile now.

Here are two error messages I see all the time now, about once per day:

UPDATED:  I’ve found a third message that belongs in this same family, with same root cause and same fix, now included below.

PowerPivot Formula is Invalid Error

PowerPivot Element Not Found Error

PowerPivot The command was canceled error

I typically get the first when I’m adding a measure, the second when I am trying to put a field on a slicer, and the third when I am adding a field from a table I just added to the model.

In my experience, these are all caused by exactly the same thing.

The first error message is completely misleading.  The third is uber-vague.  The second is very much on target. 

How to fix this

Don’t worry, your workbook is fine.  All you really have to do is get Excel, the addin, and the PowerPivot db up to date with respect to each other, because something somewhere has gotten out of sync.

There are four fixes you can try, and I recommend you try them in order since each is more time consuming than the previous.  After each step, retry adding the measure (or field) that failed to see if it’s fixed.

  1. Make the pivot update somehow – click a slicer, add a different field, filter it, etc.
  2. Right click a cell in the pivot and choose Refresh
  3. NEW:  Add a calculated column in the PowerPivot window and then delete it (this worked for Maurice Prather where everything else, including tricks 4 and 5, failed)
  4. Save and close the workbook, close Excel completely, reopen workbook
  5. Close Excel, go and clean out the Vertipaq files from your temp folder, reopen workbook

For details on how to find the Vertipaq temp folder, see this entry in the FAQ:

http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=128

12 Responses to Two common PowerPivot error messages

  1. [...] actually the reason why you see the three most common PowerPivot error messages – Excel’s “picture” of the PowerPivot db is [...]

  2. Mike Cuchna says:

    Thank you. I was going nowhere and this solution got me back on track.

  3. Matthew Perren says:

    Gah! I’ve tried ALL of these and I’m still seeing the stupid message!!!!!

  4. Bob Phillips says:

    I have been battling with the ‘invalid formula’ error all morning, and none of the suggestions shown here worked for me.

    What I was trying to do was to link a small table of labels and values into my PowerPivot model (which was sourced from a SQL Server database) so that I could create a slicer based on the labels, and create measures in my model that used the values, therefore getting a dynamic view of the values in my report.

    As I said, it kept failing for me as soon as I created a measure on this table. It checked out okay in the DAX dialog, but then threw the error when I OKed out.

    I had been adding this linked table after having first built my PowerPivot model. If I reversed the sequence, created the table, added it to PowerPivot as a linked table, and then did ‘Get External Data’ to build my ‘real’ model, it worked.

    Very odd, but it is working now.

  5. Tim says:

    None of these fixes resolved the problem for me. And unfortunately, after trying fix number 4, when I re-opened Excel the new data (which I had added just before getting the error message) was completely gone.

    • powerpivotpro says:

      Hi Tim. Yes, I’m afraid that in the rarer cases of this problem, nothing works. But Step #4 wasn’t the cause of everything being gone when you re-loaded. The original problem was that the data you added actually was *never* there – otherwise Excel would have been able to use it. There are times when PowerPivot (the addin) shows you something as if it’s real, but for some reason it never made it into the PowerPivot model. And the model is the only thing that gets saved. When you get into one of these cases where the PowerPivot addin thinks a table/column/etc. exists, but the underlying model does NOT have it, I have found no way to fix it without re-creating the stuff that is only “halfway” there. Save frequently when you’re doing intensive work.

      Most of the time though when you’re seeing these error messages, the model DOES have it, it’s just that *Excel* and the model aren’t on the same page, and that’s when steps 1-4 fix it. The rare case is where *PowerPivot* and the model are out of synch, and that’s the flavor you seem to have hit. Sorry you hit this Tim.

  6. Dale says:

    Refreshing the pivot worked for me :-) Thanks for sharing your knowledge!

    Dale

  7. AW says:

    Saving workbook and reopening worked :-) Thought I was going crazy!!

  8. Sam says:

    I had the same problem as Tim, and I closed and reopened Excel and my data was missing. I readded it and encountered the same problem. It seems I cannot add the data I want to from the database as it continuously throws this error and “deletes” the data once I close the workbook. Guess I can’t rely on PowerPivot…

  9. Frank says:

    I also tried all of these and it didn’t work. What did work was creating a new pivot table based on the same data in a new sheet. And then it worked. I could drag in the measure without this error message. Hope that works for others as well.

  10. peterettery says:

    Excel 2010 throws this error “The command was canceled. Please press F1 to get the help topic “Excel Window: PowerPivot Field List” for more details” if you try to make changes to a pivot table hich would result in it writing to a merged cell. Make sure you have not merged cells anywhere near the pivot table you are trying to change.

    Excel 2013 gives a more meaningful error about not being able to write to a merged cell.

    Took me a long time to find that out, hope it helps others…

Leave a Comment or Question