Grouping Customs Column into PowerPivot

[irvinelim]

Hi all

I have the following input data that I need to be able to group them into Output PowerPivot 1 and Output PowerPivot 2. The below example is just a simplified version but I would have thousands of row and different column. Could you please help to suggest a method. Really thanks.,



Input:
[TABLE="width: 716"]
<tbody>[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]OH Truck Canada[/TD]
[TD="align: center"]Revenue Truck Canada[/TD]
[TD="align: center"]OH Truck Brazil[/TD]
[TD="align: center"]Revenue Truck Brazil[/TD]
[/TR]
[TR]
[TD="align: center"]1-Oct-17[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]$10,000[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]$20,000[/TD]
[/TR]
[TR]
[TD="align: center"]2-Oct-17[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$15,000[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$25,000[/TD]
[/TR]
</tbody><colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup>[/TABLE]

​

Output PowerPivot1:
[TABLE="width: 442"]
<tbody>[TR]
[TD="align: center"]OH Truck[/TD]
[TD="align: center"]1-Oct[/TD]
[TD="align: center"]2-Oct[/TD]
[/TR]
[TR]
[TD="align: center"]Canada[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]Brazil[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]10[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


Output PowerPivot 2:
[TABLE="width: 442"]
<tbody>[TR]
[TD="align: center"]Daily Revenue[/TD]
[TD="align: center"]1-Oct[/TD]
[TD="align: center"]2-Oct[/TD]
[/TR]
[TR]
[TD="align: center"]Canada[/TD]
[TD="align: center"]$10,000[/TD]
[TD="align: center"]$20,000[/TD]
[/TR]
[TR]
[TD="align: center"]Brazil[/TD]
[TD="align: center"]$15,000[/TD]
[TD="align: center"]$25,000[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[irvinelim]

To be more specific, please find below input and output

Input Table
[TABLE="width: 687"]
<tbody>[TR]
[TD="align: center"]Week[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]OH Truck Canada[/TD]
[TD="align: center"]Daily Revenue Canada[/TD]
[TD="align: center"]OH Truck Brazil[/TD]
[TD="align: center"]Daily Revenue Brazil[/TD]
[TD="align: center"]OH Truck ALL Country[/TD]
[TD="align: center"]Daily Revenue ALL Country[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: center"]1-Oct-17[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]$10,000[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]$20,000[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]30000[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: center"]2-Oct-17[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$15,000[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$25,000[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]40000[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: center"]3-Oct-17[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]$10,000[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]$21,000[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]31000[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: center"]4-Oct-17[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$15,000[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]$26,000[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]41000[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: center"]5-Oct-17[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]$10,000[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]$20,000[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]30000[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: center"]6-Oct-17[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$15,000[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$25,000[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]40000[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: center"]7-Oct-17[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]$10,000[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]$21,000[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]31000[/TD]
[/TR]
[TR]
[TD="align: center"]41[/TD]
[TD="align: center"]8-Oct-17[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$15,000[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]$26,000[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]41000[/TD]
[/TR]
[TR]
[TD="align: center"]41[/TD]
[TD="align: center"]9-Oct-17[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]$15,000[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]$26,000[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]41000[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="5"></colgroup>[/TABLE]


Output Pivot Table (with row items being county so you can filter by country at later stage)

[TABLE="width: 770"]
<tbody>[TR]
[TD] [/TD]
[TD="align: center"]Column Labels[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Row Labels[/TD]
[TD="align: center"]10/1[/TD]
[TD="align: center"]10/2[/TD]
[TD="align: center"]10/3[/TD]
[TD="align: center"]10/4[/TD]
[TD="align: center"]10/5[/TD]
[TD="align: center"]10/6[/TD]
[TD="align: center"]10/7[/TD]
[TD="align: center"]10/8[/TD]
[TD="align: center"]10/9[/TD]
[/TR]
[TR]
[TD="align: center"]ALL[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Sum of OH Truck ALL Country[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]17[/TD]
[/TR]
[TR]
[TD="align: center"]Sum of Daily Revenue ALL[/TD]
[TD="align: center"]30000[/TD]
[TD="align: center"]40000[/TD]
[TD="align: center"]31000[/TD]
[TD="align: center"]41000[/TD]
[TD="align: center"]30000[/TD]
[TD="align: center"]40000[/TD]
[TD="align: center"]31000[/TD]
[TD="align: center"]41000[/TD]
[TD="align: center"]41000[/TD]
[/TR]
[TR]
[TD="align: center"]Brazil[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Sum of OH Truck Brazil[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]Sum of Daily Revenue Brazil[/TD]
[TD="align: center"]20000[/TD]
[TD="align: center"]25000[/TD]
[TD="align: center"]21000[/TD]
[TD="align: center"]26000[/TD]
[TD="align: center"]20000[/TD]
[TD="align: center"]25000[/TD]
[TD="align: center"]21000[/TD]
[TD="align: center"]26000[/TD]
[TD="align: center"]26000[/TD]
[/TR]
[TR]
[TD="align: center"]Canada[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Sum of OH Truck Canada[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]Sum of Daily Revenue Canada[/TD]
[TD="align: center"]10000[/TD]
[TD="align: center"]15000[/TD]
[TD="align: center"]10000[/TD]
[TD="align: center"]15000[/TD]
[TD="align: center"]10000[/TD]
[TD="align: center"]15000[/TD]
[TD="align: center"]10000[/TD]
[TD="align: center"]15000[/TD]
[TD="align: center"]15000[/TD]
[/TR]
</tbody><colgroup><col><col span="9"></colgroup>[/TABLE]