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!
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"