Merge & Sum Rows in PowerBI Transform Data based on Multiple Criteria

scienceNsoul 0
Viewed 27

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
powerquery powerbi-desktop powerbi
1 Answers

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"
horseyride0
Related