How can I transpose only some columns of a table in power query?

Question

I have a table like this in power query:

Date	Name	OutputType	Group1	Group2	Group3	...
01/10/2021	A	Type1	565	63521	651	...
01/10/2021	A	Type2	6546	6541	13286	...
01/10/2021	B	Type1	0	5	685	...
01/10/2021	B	Type2	564	0	65	...
...	...	...	...	...	...	...

What I'm trying to do is "transpose" only the OutputType and Group headers, so that all my groups are in one column, and my OutputTypes become headers. Something like this:

Date	Name	GroupTypes	Type1	Type2
01/10/2021	A	Group1	541	984
01/10/2021	A	Group2	8971	6526
01/10/2021	B	Group3	321	98
01/10/2021	B	Group4	564	845
...	...	...	...	...

I tried unpivoting the columns, but I get only a partial result, as all my group types do become a single column. But I can't get to "transform" my OutputType values into headers.

Does somebody know how to achieve this in power query?

Sorry if my question is not that clear, my english is not the best.

Thanks for the help!

Answer 1

Click select the Date, Name and Output Type

Right click Unpivot other columns

Click select the Output Type column

Transform .. pivot column ... and choose Value as values column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date", "Name", "OutputType"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[OutputType]), "OutputType", "Value", List.Sum)
in #"Pivoted Column"