• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

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
0
Member_2_7966563
Asked:
Member_2_7966563
  • 4
  • 3
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached and look at the sheet called Pivot. Here are the steps I followed...

1) I converted the data on Sheet1 and Sheet2 into the Excel Tables and renamed them as Data1 and Data2.
2) I imported both the tables in the Power Query then and created only connections.
3) Then I Appended both the Power Queries and renamed it as Combined Data and created a connection and added it to the Data Model.
4) Then I inserted a Pivot Table from Power Pivot manage window and renamed the sheet as Pivot.
Appended-Pivottable.xlsx
1
 
Member_2_7966563Author Commented:
Thanks a lot. The solution addressed my needs perfectly, and the working demo was the cherry on top
0
 
Member_2_7966563Author Commented:
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?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
 
Member_2_7966563Author Commented:
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
0
 
Member_2_7966563Author Commented:
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.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now