Equivalent to "Group By" function on multiple columns?

[AnonymeC]

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.

[AnonymeC]

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.

[slorin · Accepted solution]

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
Pivot

Stéphane 

[AnonymeC]

Thank you it worked perfectly.

[AlienSx]

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?