Forum Discussion
Merge & Sum Rows in Transform Data based on Multiple Criteria
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...
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.
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!
Anonymous Unpivot for the win!