Forum Discussion
How can I add one cell value to a running total using Index/match like function in Power Query
Please help, I am new to Power Query in Excel and trying to piece this together. I am pulling the data from a separate script, which refreshes regularly and when it refreshes it will add new lines throughout, so this cannot be done in just a regular excel sheet formula, that I have done and figured out how to, but I cannot figure out how do to it in Power Query so when the sheet updates the correct formula is still used on the correct lines.
I currently have the table built out in Power Query, thanks to previous help on this site, which allows the report to group by Goal and calculate a running total by group starting at the bottom of the group and going upwards, which is what I want it to do, counting just the Amount. Here is where I need help, I want to now add JUST the one value in CW_Amount that is at the bottom of each individual group to the running total in that group. Everything I have found adds in the full column, and I cannot figure out how to just add in the one specific cell for that specific group.
In the example data below I have shown what I currently have, the running total of just the amount by group, and then what I want which is a running total for the amount by group with the addition of just the CW_Amount that is at the bottom/start of that specific group. The CW_Amount that I want, will have it's own specific consumer that will not be repeated for any other CW_Amount, so I would think that I could somehow do an INDEX/MATCH for that specific consumer and then pull the CW_Amount that matches the Consumer I am looking for, but I do not know how to do that in Power Query.
I have tried to do the INDEX/MATCH in excel, and add that to my running total, but that only changes the one cell, not the full running total for the entire group. So I believe this needs to be added to my Power Query M code, I am just unsure how to do this.
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.
7 Replies
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.
- dufoq3
Community Champion
Hi 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 CombinedSo 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)
