community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Pivot Tables Break

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?

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
Customer Support Engineer

Alteryx Certified Partner

@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

would creating a new sheet in excel be causing this?

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
Customer Support Engineer

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.

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

Meteoroid

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

Labels