Sum column based on criteria while grouping in Power Query

Sum column based on criteria while grouping in Power Query.

A little trick I created once while working with some data.

The data:

The required result was to get the sum of “VALUE 1” regardless and the sum of “VALUE 2” only if “GROUP BY” is equal to “Partial”.

This is my formula:

Table.Group (
    #"Data Table",
    {
        "Group By"
    },
    {
        {
            "All",
            each List.Sum ( [Value 1] ),
            Int64.Type
        },
        {
            "Partial",
            each List.Sum (
                Table.Column (
                    Table.SelectRows (
                        _,
                        each [Criteria] = "Partial"
                    ),
                    "Value 2"
                )
            ),
            Int64.Type
        }
    }
)

Resulting in this table:

Explanation:

I grouped the table using “Table.Group” with the “Group By” column as the group by column and I am creating 2 result columns “All” and “Partial” (the sum of “Value 1” only where “Criteria” equals “Partial”). The “All” result is the simple “List.Sum” on the “Value 1” column and for the “Partial” result I Filtered the table based on the “Criteria’ column only where it matched “Partial” and the I used “List.Sum” on “Value 2” from the sub-filtered table.

I hope you find my code useful!