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:

Date	OH Truck Canada	Revenue Truck Canada	OH Truck Brazil	Revenue Truck Brazil
1-Oct-17	12	$10,000	8	$20,000
2-Oct-17	10	$15,000	10	$25,000

<tbody>
</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>

​

Output PowerPivot1:

OH Truck	1-Oct	2-Oct
Canada	12	10
Brazil	8	10

<tbody>
</tbody><colgroup><col><col><col></colgroup>

Output PowerPivot 2:

Daily Revenue	1-Oct	2-Oct
Canada	$10,000	$20,000
Brazil	$15,000	$25,000

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[irvinelim]

To be more specific, please find below input and output

Input Table

Week	Date	OH Truck Canada	Daily Revenue Canada	OH Truck Brazil	Daily Revenue Brazil	OH Truck ALL Country	Daily Revenue ALL Country
40	1-Oct-17	12	$10,000	9	$20,000	21	30000
40	2-Oct-17	10	$15,000	10	$25,000	20	40000
40	3-Oct-17	12	$10,000	8	$21,000	20	31000
40	4-Oct-17	10	$15,000	7	$26,000	17	41000
40	5-Oct-17	12	$10,000	9	$20,000	21	30000
40	6-Oct-17	10	$15,000	10	$25,000	20	40000
40	7-Oct-17	12	$10,000	8	$21,000	20	31000
41	8-Oct-17	10	$15,000	7	$26,000	17	41000
41	9-Oct-17	10	$15,000	7	$26,000	17	41000

<tbody>
</tbody><colgroup><col><col><col><col span="5"></colgroup>

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

	Column Labels
Row Labels	10/1	10/2	10/3	10/4	10/5	10/6	10/7	10/8	10/9
ALL
Sum of OH Truck ALL Country	21	20	20	17	21	20	20	17	17
Sum of Daily Revenue ALL	30000	40000	31000	41000	30000	40000	31000	41000	41000
Brazil
Sum of OH Truck Brazil	9	10	8	7	9	10	8	7	7
Sum of Daily Revenue Brazil	20000	25000	21000	26000	20000	25000	21000	26000	26000
Canada
Sum of OH Truck Canada	12	10	12	10	12	10	12	10	10
Sum of Daily Revenue Canada	10000	15000	10000	15000	10000	15000	10000	15000	15000

<tbody>
</tbody><colgroup><col><col span="9"></colgroup>