I have a table that lists transactions. Among other fields, each record lists a description, an account number and an amount. I want to create a column that gives the sum of all transactions with that particular account number.
For example, if I have five records as follows, how do I create the sum column in power query?
| Description | Amount | Account | Sum Column |
| Rent: Room 103 | $ 150.00 | 1001 | $600.00 |
| Rent: Room 104 | $ 200.00 | 1001 | $600.00 |
| Rent: Room 105 | $ 250.00 | 1001 | $600.00 |
| Supplies: Room 103 | $ 67.00 | 1002 | $90.00 |
| Supplies: Room 104 | $ 23.00 | 1002 | $90.00 |
Hi Chris,
There is a very simple technique you can use for this.
First, here is the query I used, I will explain it:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Amount", Int64.Type}, {"Account", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Account"}, {{"Sum", each List.Sum([Amount]), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Account] = 1001)),
#"1001" = #"Filtered Rows"{[Account=1001]}[Sum],
#"Added Custom" = Table.AddColumn(#"Changed Type", "Sum Column", each Table.SelectRows(#"Grouped Rows", each ([Account] = [Account])){[Account=[Account]]}[Sum])
in
#"Added Custom"
All I did is to group rows by account (this will provide the sum), applied a filter on Account column to display only one account, and in the Sum column-right click on that value and choose Drill Down, this will create the step #"1001" = #"Filtered Rows"{[Account=1001]}[Sum],
I did these steps just to get the right syntax I need to use in the last step, when I add a new column with our sum. In the formula for the new column, I simply replaced the parameter I used for the filter with a dynamic reference to the Account column, combining it with the next step(drill down):
Table.SelectRows(#"Grouped Rows", each ([Account] = 1001)){[Account=1001]}[Sum]
Table.SelectRows(#"Grouped Rows", each ([Account] = [Account])){[Account=[Account]]}[Sum]
Note that in the last #"Added Custom" step, I referred to the #"Changed Type" step, skipping the steps: #"Filtered Rows" and #"1001", which can even be deleted, As I already said I needed those steps just to obtain the correct syntax for my formula.
Hope the colors I used will help you understanding what I did.
Hi Catalin,
Thanks for such a detailed answer. I appreciate the work you put into responding to my question. The color coding and attached spreadsheet are most helpful.
Awesome. Thanks.
To assist I made a 'dummy' table and renamed the approriate columns & the table to match your code.
I then copied the code across and bingo it worked. Last thing to do was convert it back to my actual column names etc
🙂
