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

17 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…

    • deanflan@outlook.com says:

      Thanks Peter – that was the problem – I had data below the pivot table and when I added a new measure to the pivot table it could not expand due to data below it.

  11. rodcot says:


    the issue which Maurice solved on top by adding a calculated column in pivot and deleting it also worked for me… thks Maurice!!

  12. Atila Akal says:

    In my case , I got the problem when I tried to add second slicer. I believe the problem occurs because using non English characters on column name and even in the rows. When I changed these characters to English ones then I could add the slicer.

  13. Ina says:

    Hello, could you help me?
    Im working with a linked table from excel and I got this error when trying to update.

    “There were errors trying to update one or more linked tables. Use Options below to see ways to fix these problems, or OK to continue without fixing them.”
    The options below are 1) remove link 2) remove table.
    Any idea how to fix the link?
    I already tried matching the formats, so they have the same format both tables. Still, the table will not update, every time I try I get that error.

  14. Shri Pather says:

    Hi,can you please assist me, im getting the following error when trying to refresh powerpivot.

    “Sorry Powerpivot cant open the data model because there was a com exception while opening”

    It goes further on to say:

    “The session ID” cannot be found.

    Please can you assist me to fix this issue.

Leave a Comment or Question