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 Date Item Code In Qty Out Qty 1/12/2024 A 100   1/14/2024 A   50 1/13/2024 B 200   1/14/202...
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

     

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