Group by a SUM function in M power BI

Question

In PowerQuery I try to sum the quantité livréeby BL 2 (i have several rows with the same BL2 number but i can't delete doublons, regarding of details of the data)

The data looks like:

I tried:

=Table.Group(#"Somme quantité livrée", {"BL 2"},{{"quantité livrée", each List.Sum([#"quantitée livrée"]), type number}}

but the function doesnt work, have the same error message "RightParen token excepted" but i don't see what should i do here (or even if its the right function to do what i except)

Basically i want to obtain the sum of the quantité livrée, quantité retournée, quantité facturée by distinct BL 2 Any idea?

I tried the Group By table proposed in answers but using it i lost others columns:

before:

And after:

horseyride horseyride · Accepted Answer · 2021-10-27T13:42:29

Why not use the group interface to create the code for you?

#"Grouped Rows" = Table.Group(#"previous_step_name", {"BL 2"}, {{"quantité livrée", each List.Sum([quantité livrée]), type number}, {"quantité retournée", each List.Sum([quantité retournée]), type number}, {"quantité facturée", each List.Sum([quantité facturé]), type number}})

== == ==

If you want to retain other columns then in the group use an All Rows operation.

then after, expand desired columns back into table using arrows on top and slightly to right of the new column

== == ==

a totally different way to do this is just adding in three custom columns to sum ql, qr and qf based on BL2 of each row. It does NOT do any grouping, so for each BL combination you'd see the same total on each row

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source,"sum_ql",(i)=>List.Sum(Table.SelectRows(Source, each [bl 2]=i[bl 2]) [ql]), type number ),
#"Added Custom2"= Table.AddColumn(#"Added Custom" ,"sum_qr",(i)=>List.Sum(Table.SelectRows(#"Added Custom" , each [bl 2]=i[bl 2]) [qr]), type number ),
#"Added Custom3"= Table.AddColumn(#"Added Custom2" ,"sum_qf",(i)=>List.Sum(Table.SelectRows(#"Added Custom" , each [bl 2]=i[bl 2]) [qf]), type number )
in #"Added Custom3"

Group by a SUM function in M power BI

1 Answers