Power Query Sum based on another column

Question

Hi,

Not sure if this question has been asked before. I have a table like below, the occurrence of ID is more than once due to the use of multiple discounts on the same order id. The amount paid is also duplicated.

ID Disc Paid
1 2 15
1 3 15
1 4 15
2 4 10
3 2 20
3 3 20

Is there an option to sum the discount and get the distinct of amount paid in the final pivot table ?

ID NO Disc Paid
1 9 15
2 4 10
3 5 20

Thanks a lot

Answer 1

Hi @NRS-BNE

The amount paid is also duplicated => An Average should do the job then
Group your table by [ID] and do 2 aggregations: Sum([Disc]) and Avg([Paid])

let  
    // Table for demo:  
    Source = Table.FromColumns({  
        {1,1,1,2,3,3,3,3,4,4},  
        {2,3,4,4,2,3,4,5,1,2},  
        {15,15,15,10,20,20,20,20,17,17}},  
        type table [ID=Int64.Type, Disc=number, Paid=number]  
    ),  
    GroupedRows = Table.Group(Source, {"ID"},  
        {  
            {"NO", each List.Sum([Disc]), type nullable number},  
            {"Disc Paid", each List.Average([Paid]), type nullable number}  
        }  
    )  
in  
    GroupedRows