Forum Discussion
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 |
....
I have several rows with the same combination of Code/Market/Factory, and would like to consolidate all these rows in one unique row per combination. I know I can do it with "Group By" function, which is convenient with a small numbers of columns, but I have over 30 columns of date of shipments. Is there an easy way to do that without doing 30 times this with Group By?
Thank you very much for your help.
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
4 Replies
Hello, AnonymeC how would you like to summarize other columns? In general, one need to group by first 3 columns and select "All rows" as "operation". This gives you the whole table. One may apply a custom (user defined) function then to modify that table to get what you want. Just let us know what would you like to do to other columns - simple sum operation?
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.
