databasepowerbipowerquery

Computing Sum of Distinct Rows


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.

My guess

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.


Solution

  • 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

    Output from above Excel formula
    enter image description here