Search code examples
powerbigroupingpowerquerypowerbi-desktopm

Group by a SUM function in M power BI

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:

enter image description here

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:

enter image description here

And after:

enter image description here

Solution

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

    enter image description here

    #"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.

    enter image description here

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

    enter image description here

    == == ==

    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"

    enter image description here