Forum Discussion

MRIZ's avatar
MRIZ
Icon for Helper II rankHelper II
2 years ago

Inserting New Column in Table SUM (UnitPrice) Group By (ReceiptNo) in Transform Data (Power Query)

Dear Members, 

I want to add a new column in a factsales table in Power Query which will summerise UnitPrice group by ReceiptNo. 
Below is the actual image:

 

What I want is new column as shown below:

 

Thanks. 
Rizwan.

Need Help

4 Replies

    • MRIZ's avatar
      MRIZ
      Icon for Helper II rankHelper II
      2 years ago

      Hi, Thanks for the response and yes it works. What if I want to check some condition like SUM UnitPrice only where RowisActive = 1 Group By ReceiptNo. 

      • dufoq3's avatar
        dufoq3
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Do you mean something like this?

        I've added such filter in GroupedRows step.

         

        Result

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLSszQHUoZKsToQARM9IxMgZQAWMAKrMDSCqzAGsfTMLOECJggVEC2mQJapnqUxXIUpwhagilgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ReceiptNo = _t, UnitPrice = _t, RowisActive = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ReceiptNo", Int64.Type}, {"UnitPrice", type number}, {"RowisActive", Int64.Type}}, "en-US"),
    GroupedRows = Table.Group(ChangedType, {"ReceiptNo"}, {
        {"All", each _, type table [ReceiptNo=nullable number, UnitPrice=nullable number, RowisActive=nullable number]},
        {"SumUnitPrice", each List.Sum([UnitPrice]), type number},
        {"SumUnitPrice RowisActive 1", each List.Sum(Table.SelectRows(_, (x)=> x[RowisActive] = 1)[UnitPrice]), type number}
    }),
    Expanded = Table.ExpandTableColumn(GroupedRows, "All", {"UnitPrice", "RowisActive"}, {"UnitPrice", "RowisActive"})
in
    Expanded