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

     

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"