Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So I have this table with locations named with IDs. Each ID can have data from multiple years, and multiple metric types. I want to merge and sum all the Water + Irrigation rows with matching ID/Year, and leave all the rest as-is. I want to do this in transform data, because I have other things I need to do with this table and data. Is this possible?
Original Table:
Row | ID | Year | Metric Type | January Use |
1 | 12 | 2022 | Water | 25 |
2 | 12 | 2022 | Irrigation | 55 |
3 | 12 | 2021 | Electrcitiy | 28160 |
4 | 12 | 2022 | Electricity | 19760 |
5 | 423 | 2022 | Water | 46 |
6 | 423 | 2022 | Irrigation | 33 |
7 | 423 | 2022 | Natural Gas | 6340 |
8 | 89 | 2021 | Electricity | 21780 |
9 | 89 | 2022 | Natural Gas | 180 |
10 | 55 | 2022 | Water | 11 |
Desired Table
Row | ID | Year | Metric Type | January Use |
1 | 12 | 2022 | Water | 80 |
2 | 12 | 2021 | Electrcitiy | 28160 |
3 | 12 | 2022 | Electricity | 19760 |
4 | 423 | 2022 | Water | 79 |
5 | 423 | 2022 | Natural Gas | 6340 |
6 | 89 | 2021 | Electricity | 21780 |
7 | 89 | 2022 | Natural Gas | 180 |
8 | 55 | 2022 | Water | 11 |
Solved! Go to Solution.
My suggestion would be to replace Irrigation as Water using replace values and then group by ID, Year and Metric Type, summing the January Use row. You can add in an index column after if it is needed.
Proud to be a Super User! | |
@Rdata Sure, there is the ability to group rows in Power Query or you can use SUMMARIZE or GROUPBY in DAX.
My suggestion would be to replace Irrigation as Water using replace values and then group by ID, Year and Metric Type, summing the January Use row. You can add in an index column after if it is needed.
Proud to be a Super User! | |
This was such a simple step forward, thank you!
Something I left out of my original solution was that I have multiple columns (one for each month), that I needed summed separately, but I actually just unpivoted them, then grouped by ID, Year, Month, and Metric and that seems to have done the trick!
@Rdata Unpivot for the win!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |