Unpivot Specific Columns

[legalhustler]

Hello,

I would like to know how I can transform this table:

Index	Region	Category	Red	Blue	Pink	Green
1	North	Fruits	5	6	3	10
2	South	Clothes	10	12	6	20
3	East	Games	15	18	9	30

To this, which basically appends red/pink underneath one another and blue/green underneath one another. I know I can create separate queries then do a merge, but was wondering if it could be done with one query:

Index	Region	Category	Red-Pink	Value	Blue-Green	Value1
1	North	Fruits	Red	5	Blue	6
2	South	Clothes	Red	10	Blue	12
3	East	Games	Red	15	Blue	18
1	North	Fruits	Pink	3	Green	10
2	South	Clothes	Pink	6	Green	20
3	East	Games	Pink	9	Green	30

 

[Bo_Ry]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToCol = Table.ToColumns(Source),
    FromCol = Table.FromColumns({ List.Repeat(ToCol{0},2),List.Repeat(ToCol{1},2) ,List.Repeat(ToCol{2},2),ToCol{3} & ToCol{5}, ToCol{4} & ToCol{6}}, {"Index","Region","Category","Red-Pink","Blue-Green"})
in
    FromCol

Book1
	A	B	C	D	E	F	G
1	Index	Region	Category	Red	Blue	Pink	Green
2	1	North	Fruits	5	6	3	10
3	2	South	Clothes	10	12	6	20
4	3	East	Games	15	18	9	30
5
6	Index	Region	Category	Red-Pink	Blue-Green
7	1	North	Fruits	5	6
8	2	South	Clothes	10	12
9	3	East	Games	15	18
10	1	North	Fruits	3	10
11	2	South	Clothes	6	20
12	3	East	Games	9	30
Sheet1

 

[shaowu459]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    t1={"Red", "Pink"},
    t2={"Blue", "Green"},
    t3={"Index","Region","Category","Red-Pink","Value","Blue-Green","Value1"},
    fx=(x,y)=>
               Table.ToColumns(
                                Table.RemoveColumns(
                                                     Table.Unpivot(Source, x, "a", "b"),
                                                     y
                                                   )
                              ),
    res=Table.Sort(
                    Table.FromColumns(
                                        fx(t1,t2)&List.LastN(fx(t2,t1),2),
                                        t3
                                     ),
                    {{"Red-Pink",1},{"Index",0}}
                  )
in
    res

Book1.xlsx
	A	B	C	D	E	F	G
1	Index	Region	Category	Red	Blue	Pink	Green
2	1	North	Fruits	5	6	3	10
3	2	South	Clothes	10	12	6	20
4	3	East	Games	15	18	9	30
5
6	Index	Region	Category	Red-Pink	Value	Blue-Green	Value1
7	1	North	Fruits	Red	5	Blue	6
8	2	South	Clothes	Red	10	Blue	12
9	3	East	Games	Red	15	Blue	18
10	1	North	Fruits	Pink	3	Green	10
11	2	South	Clothes	Pink	6	Green	20
12	3	East	Games	Pink	9	Green	30
Sheet1

 

[Bo_Ry]

I forgot Color columns

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Index", "Region", "Category"}, "Attribute", "Value"),
    Combine = Table.FromColumns(Table.ToColumns(Table.AlternateRows(Unpivoted,1,1,1))&List.LastN(Table.ToColumns(Table.AlternateRows(Unpivoted,0,1,1)),2),{"Index","Region","Category","Red-Pink","Value","Blue-Green","Value "}),
    Sort = Table.Combine(Table.Group(Combine, {"Red-Pink"}, {{"T", each (_)}})[T])
in
    Sort

Book1
	A	B	C	D	E	F	G
1	Index	Region	Category	Red	Blue	Pink	Green
2	1	North	Fruits	5	6	3	10
3	2	South	Clothes	10	12	6	20
4	3	East	Games	15	18	9	30
5
6	Index	Region	Category	Red-Pink	Value	Blue-Green	Value
7	1	North	Fruits	Red	5	Blue	6
8	2	South	Clothes	Red	10	Blue	12
9	3	East	Games	Red	15	Blue	18
10	1	North	Fruits	Pink	3	Green	10
11	2	South	Clothes	Pink	6	Green	20
12	3	East	Games	Pink	9	Green	30
Sheet1

 

[legalhustler]

Thank you both. Looks like you changed your original code and used different functions. Just curious, was that necessary in order to add the two missing color columns?

 

[legalhustler]

I forgot Color columns

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Index", "Region", "Category"}, "Attribute", "Value"),
    Combine = Table.FromColumns(Table.ToColumns(Table.AlternateRows(Unpivoted,1,1,1))&List.LastN(Table.ToColumns(Table.AlternateRows(Unpivoted,0,1,1)),2),{"Index","Region","Category","Red-Pink","Value","Blue-Green","Value "}),
    Sort = Table.Combine(Table.Group(Combine, {"Red-Pink"}, {{"T", each (_)}})[T])
in
    Sort

Book1
	A	B	C	D	E	F	G
1	Index	Region	Category	Red	Blue	Pink	Green
2	1	North	Fruits	5	6	3	10
3	2	South	Clothes	10	12	6	20
4	3	East	Games	15	18	9	30
5
6	Index	Region	Category	Red-Pink	Value	Blue-Green	Value
7	1	North	Fruits	Red	5	Blue	6
8	2	South	Clothes	Red	10	Blue	12
9	3	East	Games	Red	15	Blue	18
10	1	North	Fruits	Pink	3	Green	10
11	2	South	Clothes	Pink	6	Green	20
12	3	East	Games	Pink	9	Green	30
Sheet1

What is the "T" and [T] mean in the last part of the code? Please explain so I understand.

 

[legalhustler]

I forgot Color columns

I need to combine one more column of colors and values. Since Power Query starts with base zero, then the new column should combine rows 1 and 4 (after Unpivoted step). How would I modify the formula below to include the new column? I changed the other starting & rows to remove arguements, so no need to worry about those. Thanks!

Combine = Table.FromColumns(Table.ToColumns(Table.AlternateRows(Unpivoted,1,1,1))&List.LastN(Table.ToColumns(Table.AlternateRows(Unpivoted,0,1,1)),2),{"Index","Region","Category","Red-Pink","Value","Blue-Green","Value "}),