Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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.
I have the similar issue, Please share if you find any solution to it
@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?
You can do oit like this:
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
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.