New Column of Row subtotals in Power Query

[Charuhans]

I am trying to create a new column which will repeat the subtotals for rows. For example:

DOC_ID	AMOUNT	SUBTOTAL
A123	100	450
A123	300	450
A123	50	450
A897	-700	-400
A897	300	-400
Q456	200	200
G253	400	500
G253	100	500

The new column is needed to calulate another column. It will only display the data from the "Amount" column if the "Subtotal" column is greater than "0". For example:

DOC ID	AMOUNT	SUBTOTAL	CALCULATED
A123	100	450	100
A123	300	450	300
A123	50	450	50
A897	-700	-400
A897	-300	-400
Q456	200	200	200
G523	400	500	400
G523	100	500	100

I think the CALCULATE function in DAX might do the work but I need to caluluate this in Power Query (M). The Query has more than a dozen columns, if that matters.

Any help is much appreciated! Thank you in advance.

[v-yingjl · Accepted solution]

Hi @Charuhans ,

You can add a conditional column directly in Power Query Editor to achieve this:

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0MlbSUTI0MFCK1YFzjVG5plCehaU5kKdrboDChykONDE1A3KNoFx3U7BeE1Qu2KJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"DOC ID" = _t, AMOUNT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AMOUNT", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DOC ID"}, {{"subtot", each List.Sum([AMOUNT]), type nullable number}, {"amt", each _}}),
    #"Expanded amt" = Table.ExpandTableColumn(#"Grouped Rows", "amt", {"AMOUNT"}, {"AMOUNT"}),
    #"Added Custom" = Table.AddColumn(#"Expanded amt", "amntot", each if [subtot]>0 then [AMOUNT] else "")
in
    #"Added Custom"