Forum Discussion
How can I add one cell value to a running total using Index/match like function in Power Query
Hi Anonymous
Based on your information, you can create a blank query and put the following code to advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQwMFDSUXIEYlMI0wJIxurAZVxAQpZgprkpikwgEJtBZMygeqBG+IBMs4DImKLIuAIxRIshqoQvxGaIYWAJcwjPHWoIWAeKjB8QIzsYKuyJ8IoxSCYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Goal = _t, Consumer = _t, CW_Amount = _t, #"Running Total Current" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Goal", Int64.Type}, {"Consumer", type text}, {"CW_Amount", Int64.Type}, {"Running Total Current", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Goal"}, {{"Data", each _, type table [Goal=nullable number, Consumer=nullable text, CW_Amount=nullable number, Running Total Current=nullable number]}, {"LastCWAmount", each List.Last([CW_Amount]), type nullable number}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Consumer", "CW_Amount", "Running Total Current"}, {"Consumer", "CW_Amount", "Running Total Current"}), #"Added Custom" = Table.AddColumn(#"Expanded Data", "Running Total", each [LastCWAmount]+[Running Total Current]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"LastCWAmount"}) in #"Removed Columns"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community ChampionHi Anonymous, I've just slightly edited the code from yesterday:
Result
let
fnRunningTotal =
(myTable as table)=>
[
// _Detail = GroupedRows{[#"Seg Outs"=7000]}[All],
_Detail = myTable,
_BufferedAmount = List.Buffer(_Detail[Amount]),
_lg = List.Generate(
()=> [ x = List.Count(_BufferedAmount)-1, y = _BufferedAmount{x} + List.Last(_Detail[CW_Amount]) ],
each [x] >= 0,
each [ x = [x]-1, y = [y] + _BufferedAmount{x} ],
each [y]
),
_ToTable = Table.FromColumns(Table.ToColumns(_Detail) & {List.Reverse(_lg)}, Value.Type(_Detail & #table(type table[Running Total=number], {})))
][_ToTable],
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQwMFDSUTKFEEB2rA5c0BAsamGJKmoGIWGipjADwKQFqqglhEARMwMbCrPKHCJoDpEyRRGEmIkiZGyAcGgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Seg Outs" = _t, Amount = _t, CW_Amount = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Seg Outs", Int64.Type}, {"Amount", type number}, {"CW_Amount", type number}}),
GroupedRows = Table.Group(ChangedType, {"Seg Outs"}, {{"fn", fnRunningTotal, type table}}),
Combined = Table.Combine(GroupedRows[fn])
in
Combined
So here is my code currently:
= let
Source = Goal,
BufferedAmount = List.Buffer( Source[INV] ),
RunningTotal =
List.Generate (
() => [ x = List.Count(BufferedAmount)-1, y= BufferedAmount {x} + List.Last(Source[CW_OUTS]) ],
each [x] >= 0,
each [x = [x]-1, y = [y] + BufferedAmount{x} ] ,
each [y]
),
#"Combined Table + x" =
Table.FromColumns(
Table.ToColumns( Source )
& {List.Reverse(List.Generate)},{ Value.ReplaceType( RunningTotal, type {Int64.Type} ) } ,
Table.ColumnNames( Source ) & {"Running Total"} )
in
( xColumnName as text, MyTable as table, ValueColumn as text) =>
let
Source = MyTable,
BufferedAmount = List.Buffer( Table.Column( MyTable, ValueColumn ) ),
RunningTotal =
List.Generate (
() => [ x = List.Count(BufferedAmount)-1, y= BufferedAmount {x} + List.Last(Source[CW_OUTS]) ],
each [x] >= 0,
each [x = [x]-1, y = [y] + BufferedAmount{x} ] ,
each [y]
),
#"Combined Table + x" =
Table.FromColumns(
Table.ToColumns( Source )
& {List.Reverse(List.Generate)},{ Value.ReplaceType( RunningTotal, type {Int64.Type} ) } ,
Table.ColumnNames( Source ) & {"Running Total"} )
in
#"Combined Table + x"
The issue is I am getting an Expression. Error: 3 arguments were passed to a function which expects between 1 and 2.
Details:
Pattern=
Arguments= [List}
It was working, expect the running total was not reversed, so I added in this section of the code:
#"Combined Table + x" =
Table.FromColumns(
Table.ToColumns( Source )
& {List.Reverse(List.Generate)},{ Value.ReplaceType( RunningTotal, type {Int64.Type} ) } ,
Table.ColumnNames( Source ) & {"Running Total"} )
in
( RTColumnName as text, MyTable as table, ValueColumn as text) =>
Which I believe would then reverse the running total like I would want, but that is when the error occurred.I am not sure where I am going wrong.
- dufoq3
Hi, I provided function query. If you don't know how to use it - read note below my post. (Copy my whol query! and change only Source part)
Okay, here is your Code copied over and only slightly updated for naming convention match:
= let
fnRunningTotal =
(myTable as table)=>
[
// _Detail = GroupedRows{[#"Outs_Goal"=7000]}[All],
_Detail = myTable,
_BufferedAmount = List.Buffer(_Detail[INV]),
_lg = List.Generate(
()=> [ x = List.Count(_BufferedAmount)-1, y = _BufferedAmount{x} + List.Last(_Detail[CW_OUTS]) ],
each [x] >= 0,
each [ x = [x]-1, y = [y] + _BufferedAmount{x} ],
each [y]
),
_ToTable = Table.FromColumns(Table.ToColumns(_Detail) & {List.Reverse(_lg)}, Value.Type(_Detail & #table(type table[Running Total=number], {})))
][_ToTable],Source = Source,
ChangedType = Table.TransformColumnTypes(Source,{{"Outs_Goal", Int64.Type}, {"INV", type number}, {"CW_OUTS", type number}}),
GroupedRows = Table.Group(ChangedType, {"Outs_Goal"}, {{"fn", fnRunningTotal, type table}}),
Combined = Table.Combine(GroupedRows[fn])
in
CombinedStill getting an error of DataFromat.Error: We couldn't covert to Number.
Details:
[Table}- dufoq3
You've changed column names but when I change names too - the query is working. Make a screenshot of your Source table and also the error.