Add Values in a Column by matching conditions in other Columns and Rows

[PravenBabu]

In Power query, i  have a large database of transactions in the format as below

Date	Item Code	In Qty	Out Qty
1/12/2024	A	100
1/14/2024	A		50
1/13/2024	B	200
1/14/2024	B		50
1/14/2024	B		50
1/15/2024	A	50
1/15/2024	A		50

 I need to add the value in a new column also with the value from the past dates matching the item code as below

Date	Item Code	In Qty	Out Qty	NET
1/12/2024	A	100		100
1/14/2024	A		50	50
1/13/2024	B	200		200
1/14/2024	B		50	150
1/14/2024	B		50	100
1/15/2024	A	50		100
1/15/2024	A		50	50

Please advise

[PravenBabu]

@Omid_Motamedise @lbendlin @AlienSx @Anonymous Thanks for all your support. I used @AlienSx Solution and got the desired result good and fast. 

[Anonymous]

Hi @PravenBabu 

Hope these help:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNI3MjAyUdJRcgRiQwMDIKkUqwOWMkGWAiJTA5iMMUzGCYiNsGtyQteEW8YU2SJTZNNMsTghFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Item Code" = _t, #"In Qty" = _t, #"Out Qty" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Item Code", type text}, {"In Qty", Int64.Type}, {"Out Qty", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Item Code", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,0,Replacer.ReplaceValue,{"In Qty", "Out Qty"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Item Code"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"Data", (x)=> Table.AddColumn(x , "NET", each List.Sum(List.FirstN(x[In Qty],[Index]))-List.Sum(List.FirstN(x[Out Qty],[Index]) ))}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"Date", "In Qty", "Out Qty", "NET"}, {"Date", "In Qty", "Out Qty", "NET"})
in
    #"Expanded Data"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Omid_Motamedise]

Just copy the below code and past it into the advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNI3MjAyUdJRcgRiQwMDIKmgFKsDljNBllMAYlMDmJQxTMoJiI1waHPC0IZHyhTZMlMUA02xuSMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Item Code" = _t, #"In Qty" = _t, #"Out Qty" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"In Qty", Int64.Type}, {"Out Qty", Int64.Type}}),
    Index = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(Index, "Custom", each [a=Table.SelectRows(Table.FirstN(Index,[Index]),(x)=> x[Item Code]=[Item Code]),b=List.Sum(a[In Qty])-(List.Sum(a[Out Qty])??0)][b])
in
    #"Added Custom"

[AlienSx · Accepted solution]

let
    fx = (x) => 
        [sorted_list = List.Buffer(Table.ToList(Table.Sort(x, "idx"), (x) => x)),
        rt = List.Generate(
            () => [i = 0, r = sorted_list{0}, net = List.Sum({r{2}, - r{3}})],
            (x) => x[i] < List.Count(sorted_list),
            (x) => [i = x[i] + 1, r = sorted_list{i}, net = List.Sum({x[net], r{2}, - r{3}})],
            (x) => x[r] & {x[net]}
        )
        ][rt],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    idx = Table.AddIndexColumn(Source, "idx"),
    blank_to_nulls = Table.TransformColumns(idx, {}, (x) => if x = " " then null else x),
    group = Table.Group(blank_to_nulls, "Item Code", {"x", fx}),
    result = Table.RemoveColumns(
        Table.Sort(
            Table.FromList(List.Combine(group[x]), (x) => x, Table.ColumnNames(Source) & {"idx", "NET"}),
            "idx"
        ),
        "idx"
    )
in
    result

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNI3MjAyUdJRcgRiQwMDIKmgFKsDljNBllMAYlMDmJQxTMoJiI1waHPC0IZHyhTZMlMUA02xuSMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Item Code" = _t, #"In Qty" = _t, #"Out Qty" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"In Qty", Int64.Type}, {"Out Qty", Int64.Type}, {"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Net", (k)=> let t = Table.SelectRows(#"Added Index", each [Item Code]=k[Item Code] and [Index]<=k[Index] )   
in (List.Sum(t[In Qty])??0)-(List.Sum(t[Out Qty])??0) 
,Int64.Type)
in
    #"Added Custom"