Forum Discussion

PravenBabu's avatar
PravenBabu
Regular Visitor
1 year ago
Solved

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

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

DateItem CodeIn QtyOut Qty
1/12/2024A100 
1/14/2024A 50
1/13/2024B200 
1/14/2024B 50
1/14/2024B 50
1/15/2024A50 
1/15/2024A 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

DateItem CodeIn QtyOut QtyNET
1/12/2024A100 100
1/14/2024A 5050
1/13/2024B200 200
1/14/2024B 50150
1/14/2024B 50100
1/15/2024A50 100
1/15/2024A 5050

Please advise

Need Help
Show and Tell
  • AlienSx's avatar
    AlienSx
    1 year ago

     

     

     

    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

     

5 Replies

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    1 year ago

     

     

     

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      1 year ago

       

      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"

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
    Omid_Motamedise
    Icon for Super User rankSuper User
    1 year ago

    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"