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

[Anonymous]

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 ?

[PatSo]

@jimmywf 

Good to hear it worked.

I'm not sure if that's caused by any KB but from my observations it seems so.

As I've described in my first post, I had a 2212 version of Excel (update from December 22) and everything worked flawlessly but it didn't for my colleagues that had a 2301 version (Jan 23 update).

After updating my excel to 2301 it stopped working.

Also, if you would put a non-modified Excel file on Sharepoint/OneDrive, you would get the same error.

Again, from my observations, it has very little to do with PBI itself, as if we would remove the connection to PBI from the file, then the problem would persist.

As for the data consistency, etc. I'm not 100% sure BUT the part that needs to be removed from the file sources suggests that it has nothing to do with the data itself.

We are removing part of schema definition, so a mechanism that tells Excel how to parse incoming data.

It's possible that we are actually removing a bit too much, because there are two attributes there: requiredFeature and lastUpdateFeature. Both of them having 'TabularASDateTime' as a value. We might need to remove only the requiredFeature attribute but I haven't tested it yet.

Anyways, seems there is no harm in removing both of them.

Again, these attribute names and their value suggests that there was a feature in pivots on how to treat dates in the data but it got removed and now Excel complains about it because schema says it is a required feature.

Of course, all of this are my best guesses so keep that in mind.

[jimmywf]

@PatSoAppreciate it thank you!

[DesmondW]

A user at my company is having the same problems. Still looking for the solution. I was able to repair one broken file by editing the .xml, but it doens't fix the underlying problem.

I'm going to try rolling the excel version back to 2212 and will report if this solves the problem for now.

[Testeri]

Thanks for the XML editing tip! It seems to be working. Does anyone know when this will be fixed from Microsoft side? Editing a file is not very easy thing to do for every end user.

[PatSo]

@DesmondW, of course it's not a solution, just a workaround curing the symptoms. Can't actually imagine modifying all the files we have one by one... If I really would need to do that, I would write a script for it.

Let us know if rollback works.

@Testeri, I'm glad it works. Well, afaik, this is the only thread about this issue (I'm just wondering why; is there really so few companies/people that keeps their sw updated?). Anyways, MS seems to be ok with that. Or maybe it should be reported separately, through a customer service ticket.

[Anonymous]

Hi All,

with my IT team we removed the last version of Excel and reinstalled the December version. Since I've done this my pivots connected to powerbi work again properly.

discussing with different partners I found out that many companies are affected but nobody really check why 

[PatSo]

@Anonymous, so that might confirm my theory that there was a feature (XML indicates that) in previous versions which has been removed from Excel completly without a backward compatibility.

[amallen125]

All,  Our company had these issues and today's update (2/14/2023) appears to have corrected them.  For O365 folks, you'll need to download and install the latest version 2301; build 16026.20200.  

[Testeri]

@amallen125Thanks, great news!

[irunman1983]

I can confirm version 2301; build 16026.20200 fixed the issue. Also @PatSo thank you for that little walkthrough of extracting XML files from excel. That helps with another issue I am trying to solve.

thank you all.