error message "this pivottable was created in a later version of excel and can't be updated ..."

[Pierre_warsaw]

Hi Community,

I'm encountering a challenging situation

I created pivot table in excel files connected to powerbi data

reopening the file to refresh them is see that they are not "recognized" as live pivot table anymore

when I try to click on a filter i get the following message "this pivottable was created in a later version of excel and can't be updated in this version."

any idea how to resolve this ?

[v-yetao1-msft]

Hi @Pierre_warsaw 

Look at the error report seems to excel version problem, you create and open pivot table are in the same excel?

Best Regards,
Community Support Team _ Ailsa Tao

[parishdapbi]

We're getting similar problems with our Excel pivot tables connecting to Analysis Services since this weekend (1/29/2023). I'm on version 2301, and my machine works fine, but many others don't, whether on 2301 or 2212. 

[Testeri]

I have same problem. If my co-worker downloads "Analyze in excel" file, creates pivot-table in it, sends to me, I get that error and also "Show fields" option is greyed out behing right mouse button options.

If I do the same and send file to him it works ok. Updating Excel didn't help.

[tgalla01]

Ditto, same problem here! 2/2/23

[PatSo]

Hello guys,

We have encountered (almost) the same situation in our company.

The case is, that it affects the files that were created in the past (seems that only really old ones, like 1 year ago +). Of course, the symptom is the same: error message 'this pivottable was created in a later version of excel and can't be updated'.

My colleague reported that she has such an issue with a file. Our IT reassigned a ticket to me (BI Dept) as it seemed to be connected with PBI. I've opened the file on my computer and there were no issues. Turned out that I have an older version of Excel (2212 and she has 2301). Anyways, deleting PBI connections from the file did not work (of course, as Excel throws this error even before using the connection).

A newly exported Excel file from PBI for the same dataset did not have such a problem.

Therefore, I thought it has to be something in the files sources.

I have opened the excel file as an archive and located pivot definitions. They are located under /xl/pivotTables:

In every definition there are three attributes: updatedVersion, minRefreshableVersion and createdVersion.

Value of createdVersion was 3 so I've changed that to 6:

This solution worked for that specific file but didn't for the others. Found out that some files, besides pivotTables folder have also pivotCache folder with same xml files. Applying above modification to them did not work neither.

So I started digging again and relised that there is also another thing regarding pivot version at the very bottom of these files:

It seems that removing this solves the problem.

Did that to two files so far so it might not work for all.

[parishdapbi]

@PatSo, Thanks. We're testing at our company, and seems to be working so far, but not sure that the extra XML doesn't get added back in later.

[jimmywf]

@parishdapbi @PatSo 

What did you do to make your excel sheets work again for these pivottables pointing to the powerbi? Our company recently encountered this issue since last week.

[PatSo]

@jimmywf 

Well, I briefly described the process in my previous message but I'll try to make it more clear.

Of course, before doing anything, I'd reccomend making a copy of the excel file in question.

Every excel file that is saved with xlsx extension is nothing more but an archive consisting of several XML files (that's where the last 'X' comes from; same for docx, etc.). It means that it can be opened with almost any archive manager, like RAR, ZIP or, in my case, 7ZIP.

After you open the file as an archive, you need to navigate to the \xl\pivotTables\ folder. You will find there files like pivotTable1.xml, pivotTable2.xml, etc (their number depends on the amount of actual pivots in whole workbook).

After navigating to that folder, you should extract all these files to some location on your computer and edit them with some decent text editor (windows notepad is a no go), like Notepad++, Sublime or Visual Studio Code. You should leave the archive open.

You need to locate the node (line) that consists of 'TabularASDateTime' and remove it completely. I have marked it on the last screenshot. You need to make sure that you delete the whole node (including opening <ext> and closing </ext> tags).

Above operation needs to be repeated for every file that was found under \xl\pivotTables\ directory.

After all the files have been altered and saved, you need to move them back to the archive. Simply drag and drop them on the \xl\pivotTables\ directory. This way the archive will get altered.

You can now close the archive and open the excel file.

[PatSo]

A small update to that:

You actually need to modify the files not only from \xl\pivotTables\ directory but also from the \xl\pivotCache\ directory.

[jimmywf]

@PatSo

Nice, thank you. This worked on our test excel and we're going to test a couple of more.

Do you know what's the root cause of the issue by chance? We're trying to figure out if it's KB that's deployed by Microsoft and if it's a excel versioning or an issue on the powerbi end?

Also does this fix have any concerns of the numbers or formula causing inconsistency of numbers that's calculated?