I'm working in Power Query, and have the following table:
Year | Type | Value |
---|---|---|
2022 | A | 13 |
2022 | B | 5 |
2023 | A | 11 |
2023 | B | 36 |
2024 | A | 9 |
2024 | B | 22 |
I'd like to add the values of type A and type B together, to produce the following table:
Year | Value A + Value B |
---|---|
2022 | 18 |
2023 | 47 |
2024 | 31 |
Is this possible in Power Query? I'd prefer not to do this "manually" using cell-specific formulas in Excel. The biggest issue I run into is not being able to reference other rows based on a specific cell value.
Bonus points if you know how to do this, replacing simple summation with a more complicated function.
I could figure out how to do it if I were able to manipulate the first table into something that looks like
Year | Value A | Value B |
---|---|---|
2022 | 13 | 5 |
2023 | 11 | 36 |
2024 | 9 | 22 |
This feels close to a pivot, but not quite.
It is a simple Table.Group
operation
M Code from the Advanced Editor
let
//change next line to reflect your actual data source
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Type", type text}, {"Value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Year"}, {
{"Sum", each List.Sum([Value]), type nullable number}})
in
#"Grouped Rows"
In the User Interface, you would
By the way, your guess
table is, in Power Query, a simple Pivot
Also, in Excel, if you have the latest 365
version, you can reproduce the output using a Table with structured references, so you do not have to deal with varying cell references. For example: =GROUPBY(your_table[[#All],[Year]],your_table[[#All],[Value]],SUM,3,0)
will produce the same output as Power Query, and will autoupdate when you change your original data