How can I add one cell value to a running total using Index/match like function in Power Query

[JenSme]

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. 

[dufoq3]

Hi @JenSme, 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

[JenSme]

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)

[JenSme]

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
Combined

Still 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.

[Anonymous · Accepted solution]

Hi @JenSme 

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.

[JenSme]

Thank you!! With a few motifications I was able to use this code perfectly!