Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Pivot Tables Break

jonathangonzales
8 - Asteroid

When I output to an excel file with Pivot tables the Pivot tables are no longer Pivot Tables.

This has happened in the past and I rebuild the Pivot tables and that would no longer continue.

Now everytime I run my workflow the pivot tables have to be rebuilt.

 

Does anyone know how I can correct this issue?

9 REPLIES 9
StephenR
Alteryx
Alteryx

We've experienced this quite a bit.  The only solutions that we've found are to output to a new excel file, then create the pivot tables within that new file and make no changes to that file ever after that. This solution is extremely fragile and we save as a new file whenever we need to do more work after that, but it can withstand the automatic data refresh if you don't touch the file for any other purpose.  The other solution is to save the file separately from the file containing the pivot tables, then copy the data into the file containing the pivot tables.  This is a significant challenge when using Alteryx and definitely needs some work.

Regards,
Stephen Ruhl
Principal Customer Support Engineer

jrgo
14 - Magnetar

@jonathangonzales,

 

Pivot tables in Excel is simply references to another data set. Alteryx does not have the ability to CREATE/MODIFY a pivot table as that's an Excel function. I'm not sure how your Alteryx Workflow is configured or what your Excel workbook looks like, so this is primarily assumptions...

 

  • Review your Output tools configuration
  • Make sure it's configured to "Overwrite Sheet (Drop)" so that it does not alter any other worksheet other than the one that contains the data referred to by the pivot table
  • If your pivot table and data are contained on the same sheet, I'd suggest putting one on another sheet
    • it is possible to tell Alteryx to output to a specific range if for some reason you need to keep the pivot table and data in the same sheet, but it does add a little more complexity... there are posts/KB articles that explain how to do this if interested

As mentioned, Alteryx can't manipulate your pivot tables, but it can update the data range it's looking at. As such, when Alteryx updates the data source, you will still need to open the Excel file and update the pivot table data as you normally would.

 

Hope this helps, but if you have any screenshots or able to share any files that we can run to replicate your issue, that'd be helpful to provide you a better solution.

 

Best,

 

Jimmy

jonathangonzales
8 - Asteroid

would creating a new sheet in excel be causing this?

StephenR
Alteryx
Alteryx

If you are creating a new sheet, the pivot will not be referencing that new sheet.  You need to overwrite the sheet that the pivot table is referencing.

Regards,
Stephen Ruhl
Principal Customer Support Engineer

jhartwells
5 - Atom

yes if you make a new sheet (using a workflow) in a workbook that contains pivot tables (even if the pivot tables do not use "the new sheet") the pivot tables will break. 

 

but if you manually add the sheet and then run the workflow you can drop and replace the sheet all you want with out causing issues. 

 

it's very annoying but that is the only work around i have found so far.

Kenneth_L
6 - Meteoroid

I am experiencing the same problem.  Output to an existing Excel file.  The old pivot table is in another worksheet, unrelated.  After output, the pivot table breaks (similar to "paste as value").  All formula pointing to the pivot generate errors.  

 

The output file opens with an "unreadable content" warning.  I have to select "yes" to proceed when Excel asks to repair. 

Kenneth_L
6 - Meteoroid

It appears that the solution for my case is to delete all errors in name manager within Excel. Hope it helps.

Kdeshmukh
7 - Meteor

The problem is with Excel and not Alteryx, instead of ".XLSX" try saving the output file in some other format like ".CSV" or  ".XLSM" 

For me, saving it macro enabled file worked.

 

Hope this helps!! 

cclark
5 - Atom

the .XLSM solution works for me too - thanks!

Labels