skip to Main Content

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:

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 23 Comments
    1. I found that 1 through 4 above didn’t work for me. I didn’t try 5. However, I did check the linked table and found that for the data in column A (date field) the last row was blank. So I thought, aha! I had just expanded the table earlier, removed one date (it was a holiday) that I didn’t want included and copied and pasted the dates up one row. Leaving the last row of the table blank. Clearly the Creators are rather persnickety. Once I corrected that, the tables uploaded fine. I’m still learning, so give me a break everyone. And it’s the holidays!

  1. 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.

  2. 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.

    1. 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.

  3. 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…

  4. 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.

  5. 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…

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

  7. 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.

  8. 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.

  9. 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.

  10. I am getting the canceled error when I am just trying to add the pivot table, I import the data to power pivot and then click the pivot table in the power pivot window and it gives me the canceled error. can someone help? thanks!

  11. I have been getting this as well. Seems like it doesn’t like to add measures from a new datatable inserted into an existing PowerPivot. I tried refreshing all PowerPivot tables as well as the PivotTable multiple ways with no luck. I had also tried closing Excel multiple times with no luck… uggghh. Finally I clicked on a slicer selection and retried (with little hope that it would work) and what do you know… It Worked… But now I am better and happier this morning.

  12. I have to correct my prior statement. It seemed to work but then when I refreshed my slicers (took off filters) I received the error about “overlapping of pivot tables”. So I took the last step #5 and deleted out all of the Vertipaq temp files/folders… and this still didn’t resolve issue. I then noticed that my slicers were not setup through the PowerPivot FieldList but through the PivotTable. I decided as a last ditch effort to delete out my slicers and then added them back via the PowerPivot FieldList as Slicers_Horizontal… and now it works beautifully and my slicers are cleaner too.
    Not sure why but it worked and is working still.

  13. I run into these issues a lot. One of the fixes ends up working but I keep having to do it. What could the problem be? Am I working with too large a file or do I just need to re-install?

  14. Another late response. I just had a very similar error when adding a measure (I know works) to a pivot table. It turns out that adding the measure would cause the pivot to overlap with another. Normally that gives a different error but in this case it was the first error above. Easy fix but surprising.

Leave a Reply

Your email address will not be published. Required fields are marked *