Running Subtract by Group

Hi kivancc94 

You can refer to the following example.

Create two blank query,then put the following two queries to the Advanced Editor in power query Editor.

1.Create a custom function.(Query1)

 

(Sum) =>
let
    #"Added Index" = Table.AddIndexColumn(Sum, "Index", 1, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if [Index] = 1 then [Stock] else 0-[Stock]),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each List.Sum(List.FirstN(#"Added Conditional Column"[Custom],[Index]))),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom.1", "Sum"}})
in
    #"Renamed Columns"

 

2.Put the function to the table (Query 2)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI31jcyV9JRMjQ3MjUA0kbGZpZKsTpUkDQ2BEmamBqB5UwM8cvFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Starting Date" = _t, Stock = _t, HE.Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Starting Date", type date}, {"Stock", Int64.Type}, {"HE.Code", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"HE.Code"}, {{"Count", each _, type table [Starting Date=nullable date, Stock=nullable number, HE.Code=nullable number]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "Query1", each Query1([Count])),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query1", {"Starting Date", "Stock", "Sum"}, {"Starting Date", "Stock", "Sum"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query1",{"Count"})
in
    #"Removed Columns"

 

Output

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.