Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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. 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.