Group by ID and sum a column based on a third column with Power Query.

[DevM01]

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? 

[artemus · Accepted solution]

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.

[vinod473]

I have the similar issue, Please share if you find any solution to it

[DevM01]

@artemus am not sure if I should post this as a new question, but I want to filter the "years" as >= current year, how do we do that? 

[artemus]

You can do oit like this:

[artemus]

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

[DevM01]

Thanks @artemus .. how do I do that and keep all other columns I already chose in a previous step? When I do the grouping I lose all other columns I had already. For those other columns, they are shared between all, so I'd like keep them the same.