Forum Discussion
Solved
Equivalent to "Group By" function on multiple columns?
Hello, I have a huge database that basically looks like: Code Market Factory Jul 21 Aug 21 Sep 21 .... Apr 23 May 23 Jun 23 3424555332 DACH XXX 0 34 24 ... 22 50 12 ...
Hi
UnPivot then pivot
let
Source = YourSource,
UnPivot = Table.UnpivotOtherColumns(Source, {"Code", "Market", "Factory"}, "Attribute", "Value"),
Pivot = Table.Pivot(UnPivot, List.Distinct(UnPivot[Attribute]), "Attribute", "Value", List.Sum)
in
PivotStéphane
Hello AlienSx , yes that's right for the rows that have the same combination of Code/Market/Factory, I would like to do a sum.
Example:
If I have originally this table:
| Code | Market | Factory | Jul21 | Aug21 | Sep21 | ... | Apr23 | May23 | Jun23 |
| 0000011 | DACH | Plant1 | 0 | 23 | 8 | ... | 0 | 76 | 6 |
| 0000011 | DACH | Plant1 | 2 | 4 | 7 | ... | 0 | 5 | 1 |
It should consolidate into:
| Code | Market | Factory | Jul21 | Aug21 | Sep21 | ... | Apr21 | May21 | Jun21 |
| 0000011 | DACH | Plant1 | 2 | 27 | 15 | ... | 0 | 81 | 7 |
Many thanks.
slorinSuper User
Hi
UnPivot then pivot
let
Source = YourSource,
UnPivot = Table.UnpivotOtherColumns(Source, {"Code", "Market", "Factory"}, "Attribute", "Value"),
Pivot = Table.Pivot(UnPivot, List.Distinct(UnPivot[Attribute]), "Attribute", "Value", List.Sum)
in
PivotStéphane
Thank you it worked perfectly.