Recurring save problem when working with Power Query and Power Pivot

[olivierhbh]

Hello all!

I have a few files in which I use both Power Query and Power Pivot and a recurring and very annoying error being thrown in all of them, for the same kind of scenarios.

The error happens when saving:
"Errors were detected while saving *****. Microsoft excel may be able to save the file by removing or repairing some features. To make the repairs in a new file, click continue. To cancel saving the file, click Cancel."

After this message shows up, I can't do anything and I lose my changes.

It happens sometimes when I do some changes on PP (fed with PQ) then refresh my tables but it happens alway when I edit a PQ table, update the connection string in PP, refresh PP then refresh my pivots.

Since this error starting to happen (= since I started using PQ), I got used to save whenever I made a change of 2/3 steps. PP and PQ work perfectly when not both used in a file.

Of course I searched for a solution on the web and I often saw this error message, but none of the solutions I read applies to my case.

Is this something that has already happened to one of you?

If you could provide me some help that would be awesome.

Thank you,
Olivier.

 

[ImkeF]

Hi Olivier,
if you pass PQ table to PP, these tables should never be edited in PP. So just make sure that the column formats and names are like you want them in PP and create all columns you need in PQ as well. It is a known bug that problems occur once PQ tables are changed in PP. You can add measures of course.

Are you using 2010? I've also experienced all sorts of weird problems when loading PQ to PP in 2010 and finally gave up there

Make sure you're always using the latest PQ version.

 

[olivierhbh]

Hello ImkeF,
I'm not good enough to think about the best relational model, the extra columns and measures before starting to play with the data, then I will need to edit PQ even after loading the data to PP, it works, but not perfectly haha.
However I heard your advice and I'll try to do the most possible in PQ before loading to PP.

Indeed I'm Using 2010, regularly updating PQ. I also meet many weird issues, there are some that I avoid by changing my usual behavior, like saving with the active cell out of a pivot table, or launching the PP window before doing anything else when I open a PP file.

Unfortunately I guess my company is not even thinking about upgrading Office yet.

Thanks for your help ImkeF!

Olivier.

 

[ImkeF]

Then you could think of loading PQ to table and fetch the tables from Excel (for tables that fit into Excel worksheets - will save you trouble).

With Power Update you could alternatively load to SQL Server instead of the PP data model and fetch the data from there: Power Query as an SSIS Alternative - PowerPivotPro PowerPivotPro

 

[olivierhbh]

Oh really? I wasn't aware of that, that could be a solution indeed (even if triples the weight of the 600000 rows file I tested it on :/). I won't have tables bigger than a million rows, the max would be 2 million rows with 100 columns in total in one file, would you be able to tell me if that would fit?

I didn't know Power Update either, it's very interesting, would that work if a user who doesn't have PP or PQ installed tries to refresh a file?

I can't play with SQL server unfortunately, I can just read our databases.

Thanks!

 

[olivierhbh]

After a few tries of editing PQ, updating my excel tables, then my PP linked tables I have the same error.... :
"Errors were detected while saving *****. Microsoft excel may be able to save the file by removing or repairing some features. To make the repairs in a new file, click continue. To cancel saving the file, click Cancel."

I guess I'll have to stay remain with my old problems..

 

[ImkeF]

What a pain...
What I did was to keep PQ and PP in separate files. But of course then you have to find a way how to refresh your PQ files if users shall only access the PP files.