Need to Make Pivot Table from 4 Data Tables

[lmcauthen00]

I have 4 tables with the same columns (sales, 4 different revenue accounts) and need to make 1 pivot table with the combined data but haven't had any luck. I have tried using the data model, power pivot, power query... Can anyone help me?? I can obviously combine all tables and make 1 pivot, but I have to keep them separate as they are used for other calculations at the same time.

 

[sandy666]

don't open just download: excel file.zip

 

[lmcauthen00]

Sandy, yes this is pretty much what I was looking for. Can you tell me the steps you took?

 

[sandy666]

sure,
make all ranges what you want as tables
load these tables to PQ editor
clean these tables: remove all unnecessary columns from each table (stay with: Name, Customer Type, Revenue Type and from Jan-2018 to Dec-2018 = 15 columns) (you can do that after Append - it's up to you)
Use Append option to join these tables
select first three columns (Name, Customer Type, Revenue Type) then choose from the menu: UnPivot Other Columns
Close&Load to ... As connection only
---
Insert - PivotTable
Use an external data source - Choose connection - select Query - Append1 - ok
choose place: New worksheet or Existing worksheet
put Name, Customer Type and Revenue Type to the ROWS Area
put Attribute to the COLUMNS Area
put Value to the VALUES Area
change Value to SUM if necessary
Report Layout - Show in Tabular Form
Subtotals - Don't show subtotals

that's all I think