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
Member_2_7966563Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.