Merge & Sum Rows in Transform Data based on Multiple Criteria

[Anonymous]

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

[Greg_Deckler]

@Anonymous Sure, there is the ability to group rows in Power Query or you can use SUMMARIZE or GROUPBY in DAX.

[jgeddes · Accepted 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.

[Anonymous]

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! 

[Greg_Deckler]

@Anonymous Unpivot for the win!