Grouping columns by date

[Felipe_Amancio]

Hi masters,

I came across the situation below where I have a table that have 3 columns for every month of the year 

			TC	COF	EPM	TC	COF	EPM	TC	COF	EPM
Account	Type	Product	Jan-23	Jan-23	Jan-23	Feb-23	Feb-23	Feb-23	Mar-23	Mar-23	Mar-23
0001	Revenue	AAA	50	20	15	45	35	20	25	35	45
0002	Revenue	AAA	-	30	8	38	28	13	18	-	20
0003	Expense	BBB	15	23	8	38	28	13	18	-
0004	Revenue	DDD	35	20	20	35	45	50	20	15	45
0005	Revenue	AAA	20	-	-	15	38	8	38	-	-
0006	Revenue	CCC	50	20	15	45	35	20	25	35	45
0007	Expense	BBB	43	13	8	38	43	13	8	38	28
0008	Expense	BBB	45	50	20	15	45	-	15	45	35

I would like to group the columns with the same month, having only the total per month (see below). Is there an easy or right way to do it (like a dynamic columns grouping) considering that the original table has thousands of rows and 36 columns of month (3 x 12 months).

Account	Type	Product	Jan-23	Feb-23	Mar-23
0001	Revenue	AAA	85	100	105
0002	Revenue	AAA	38	79	38
0003	Expense	BBB	46	79	18
0004	Revenue	DDD	75	130	80
0005	Revenue	AAA	20	61	38
0006	Revenue	CCC	85	100	105
0007	Expense	BBB	64	94	74
0008	Expense	BBB	115	60	95

Thanks

Felipe Oliveira

[StevenP · Accepted solution]

Hi @Felipe_Amancio,

Utilizing Transpose and Cross Tab tools will enable you to group these headers in a dynamic and easy way.

First remove the top first header either in the input tool configuration or through a dynamic rename tool. From there Transpose the data by grouping the main key columns (Account, Type, Product) and setting the dates as the data columns along with Dynamic or Unknown Columns then remove the trailing numbers generated from the duplicate header warnings. After that's done you can just simply Cross Tab the data back into the format you are looking for. Adding in some extra steps in between can ensure the columns are sorted in the right way.

See attached for a completed workflow example.

[Felipe_Amancio]

Thanks StevenP! it worked great!