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? I was thinking of maybe making a custom column that does a SUM IFS of some kind, and then visually filtering out all the irrigation rows? Please don’t suggest a measure or something that isn’t functional within Transform Data. I need to merge this table to another one once I complete this step.
Original Table:
| Row | ID | Year | Metric Type | January Use |
|---|---|---|---|---|
| 1 | 12 | 2022 | Water | 25 |
| 2 | 12 | 2022 | Irrigation | 55 |
| 3 | 12 | 2021 | Electricity | 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 | Electricity | 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 |
>Solution :
Just right click Metric Type and replace Irrigation with Water, then click select [ID, Year and Metric type] and group by, and take sum of January column
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,"Irrigation","Water",Replacer.ReplaceText,{"Metric Type"}),
#"Grouped Rows" = Table.Group(#"Replaced Value", {"ID", "Year", "Metric Type"}, {{"January Use", each List.Sum([January Use]), type nullable number}})
in #"Grouped Rows"