Log In
Register
Home
Dashboards
Power Query
Power Pivot
December 8, 2017
Offline
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 |
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
Answers Post
Dashboards
Power Query
Power Pivot
December 8, 2017
Offline
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.
March 21, 2019
Offline
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
🙂
1 Guest(s)
