asked on

How to create Pivot Table by Appending Two Data Tables

I have two tables in an Excel file with exactly the same structure. I wish to have a PivotTable that has as a source, the appended version of both tables put together. Attached is a file for demo, and also a mockup of the expected output.

Could you please help?
Appended-Pivottable.xlsx

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_7966563

ASKER

Thanks a lot. The solution addressed my needs perfectly, and the working demo was the cherry on top

Member_2_7966563

ASKER

Thanks to your advice, I switched from regular Pivot to PowerPivot, but this had an undesired consequence: the check box "Show Items with no data" got greyed out. Now I can no longer force columns to show up, even when no data is present due to a certain filter/ slicer.

As a result, depending on my slicer, the number of columns in the Pivot table keeps changing. With a regular Pivot table, with the check mark enabled, I got a clean table that remain the same width irrespective of the slicers.

How can I fix this?

Subodh Tiwari (Neeraj)

In that case, in the third step I mentioned in my last reply, don't create connection and load it to the data model instead load it to the worksheet so that the combined data will be placed as an excel table on the worksheet and then you can create a normal pivot table to get the desired output.

Member_2_7966563

ASKER

Another problem I faced by switching to PowerPivot is that Calculated Fields dont work the same anymore.

I would like to display ratio of Column A total to Column B total. In the example, it is supposed to be 0.9241

I tried the formula: = [Sum of Column A]/[Sum of Column C]

But I got the answer : 0.970377937

Then I treid the formula =[Column A]/[Column C], but then I got the error

Calculation error in measure 'CombinedData'[AdividedByC]: The value for 'Column A' cannot be determined. Either 'Column A' doesn't exist, or there is no current row for a column named 'Column A'.
Appended-Pivottable.xlsx

Member_2_7966563

ASKER

Subodh, that would certainly solve both of my issues, but it would bloat the size of the file, and also require the user to do two Refreshes, any time data changes. That's why I liked your solution of creating a Pivot directly from the Powerquery.

Subodh Tiwari (Neeraj)

You created a calculated field by using the formula =[Sum of Column A]/[Sum of Column C] which returns the correct output whereas probably you need =[Sum of Column A]/[Sum of Column B] which would return your desired output 0.924124514.