Automation and Pivot Tables

[trentsheffield]

Hey community,

Looking for a solution to avoid breaking my pivot table when updating its underlying dataset. Has anyone found a viable workaround for this problem?

[caltang]

I faced a similar problem to this. What I did was to add dummy data onto the main dataset. 

For example, if I have:

Product | Category | Group | Amount

AYX | Software | IT | xxx Amount

I'll just add a sample dataset that has all the possible fields. Group by each value and add them into your dummy dataset, then Union with the main dataset, use the data that streams out of the Union to build your pivot tables.

This ensures your Pivot table never breaks regardless of the new data having it or not. Because sometimes your fields go missing right, having dummy data ensures it will always be there + doesn't affect your Sums. It does affect your Counts, so be mindful of that if you use Summarize Tools after your Pivots.

[jfha97]

I agree with @caltang 's answer, I intuitively got to the same answer after trial and error. For me, in the end, the process felt clumsy, so I decided to move away from pivot tables as my delivery/reporting solution. Depending on your case and your organization's capabilities, I suggest you transition to PowerBI or Tableau for reporting (unless flat/raw files are needed).

Alteryx + BI tools are an incredibly powerful combo, and you will always ensure your stakeholders have greater visibility and insights. You can make a simple dashboard much more in-tune with what your stakeholders want to analyze and visualize, while at the same time leveraging the right tool for the job. Excel is great to quickly organize your data and make simple conclusions, but visualization tools will take a step further. 

Use Alteryx for the heavy lifting i.e., formatting, cleaning, blending, parsing, and organizing data; and visualization tools for your delivery/reporting. The catch: users won't easily adapt to PowerBI and Tableau, but with a little bit of effort and a couple of use cases, you can get it done.

These are my two-cents, while uncalled for, I am suggesting because of the "Best Practices" tag.

Let me know if you have any further questions!