Forum Discussion

SiGill1979's avatar
SiGill1979
Frequent Visitor
6 years ago
Solved

Sum If

How can I sum a column if the value is less than a certain value and matches another value.   My Data is like this A B C Opening Stock 59 12508.92 201925   59 -2843.1 201926 1250...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Ooops copied the wrong query AND I did not put it in code block.

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}, {"Opening Stock", type number}})),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"AllRows", each _, type table}}),
    Transform = Table.TransformColumns(#"Grouped Rows",{{"AllRows", 
(tab) => Table.AddColumn(tab, "RunningTotal", each List.Sum(Table.SelectRows(tab, (row) => row[C] < [C])[B]))
, type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Transform, "AllRows", {"B", "C", "RunningTotal"}, {"B", "C", "RunningTotal"})
in
    #"Expanded AllRows"
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Simon,

I tried to take a shortcut based on the sample size loaded. I am hoping this code will be more complete and faster

 

Note that because I am grouping by column A, row[A] = [A] is not necessary.

 

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}, {"Opening Stock", type number}})),
#"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"AllRows", each _, type table}}),
Transform = Table.TransformColumns(#"Grouped Rows",{{"AllRows",
each Table.AddColumn(_, "RunningTotal", each List.Sum(Table.SelectRows(Source, (row) => row[C] < [C])[B]))
, type table}}),
#"Expanded AllRows" = Table.ExpandTableColumn(Transform, "AllRows", {"B", "C", "RunningTotal"}, {"B", "C", "RunningTotal"})
in
#"Expanded AllRows"

 

Regards,

Mike

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Ooops copied the wrong query AND I did not put it in code block.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}, {"Opening Stock", type number}})),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"AllRows", each _, type table}}),
    Transform = Table.TransformColumns(#"Grouped Rows",{{"AllRows", 
(tab) => Table.AddColumn(tab, "RunningTotal", each List.Sum(Table.SelectRows(tab, (row) => row[C] < [C])[B]))
, type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(Transform, "AllRows", {"B", "C", "RunningTotal"}, {"B", "C", "RunningTotal"})
in
    #"Expanded AllRows"
  • SiGill1979's avatar
    SiGill1979
    Frequent Visitor
    6 years ago

    This looks perfect. Thanks so much!!