Forum Discussion
Sum If
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"
The code below presumes that your data is in a table named "Table2". The process is to add an index column to the table that starts at 0. Then take the First chacters up to the index amount from the prior table B column and sum them up.
This technique may be slow if you have a large table. In that case, we would add a List.Buffer as a separate step on the #"Added Index" table.
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type number}, {"C", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Opening Stock.1", each List.Sum(List.FirstN(#"Added Index"[B],[Index])))
in
#"Added Custom"Regards,
Mike
Thanks Mike
That is very close to what I am looking for, thank you, does take a while to run though.
But it needs to look at column C and only sum anything less than the current value in column C
And also needs to look at column A and only sum if the current value matches column A. Hope that makes sense.
Hence the =SUMIFS(B:B,C:C,"<"&C3,A:A,A3) is it was in Excel
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
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"This looks perfect. Thanks so much!!