Forum Discussion
Helper IGroup by ID and sum a column based on a third column with Power Query.
I have data coming back that look like this:
| Id | Year | Amount |
| 1 | 2010 | 15 |
| 1 | 2010 | 20 |
| 1 | 2012 | 20 |
| 2 | 2010 | 10 |
| 2 | 2011 | 30 |
| 2 | 2011 | 40 |
| 2 | 2011 | 15 |
| 3 | 2012 | 10 |
| 3 | 2013 | 20 |
| 3 | 2013 | 20 |
I want the end result to be like this:
| Id | Year | Amount |
| 1 | 2010 | 35 |
| 1 | 2012 | 20 |
| 2 | 2010 | 10 |
| 2 | 2011 | 85 |
| 3 | 2012 | 10 |
| 3 | 2013 | 40 |
So for each Id, the year should be unique, and the amount should be summed for that year. Can I do it in Power BI query editor? Do I need to write code for that?
That depends on what you want to show in each other column, since you have fewer rows, you are going to have to determine what to show when those rows are merged for those other Columns. For example, you could add additional aggregates for each column using the max aggregate.
6 Replies
artemusMicrosoft Employee
What you need to do is a group by:
1. Select both the Id and Year column
2. Choose Group By button.
3. In the aggregations, choose Sum, and for the column choose Amount
4. Name the column Amount
- artemus
That depends on what you want to show in each other column, since you have fewer rows, you are going to have to determine what to show when those rows are merged for those other Columns. For example, you could add additional aggregates for each column using the max aggregate.
