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:

Group ByCriteriaValue 1Value 2
Group 1All3520
Group 1All3040
Group 1All3540
Group 1Partial2520
Group 1Partial3550
Group 1All5045
Group 2Partial3045
Group 2All4030
Group 2Partial2030
Group 2Partial1530
Group 2All4520

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"
            each List.Sum ( [Value 1] ),
            each List.Sum (
                Table.Column (
                    Table.SelectRows (
                        each [Criteria] = "Partial"
                    "Value 2"

Resulting in this table:

Group ByAllPartial
Group 121070
Group 2150105


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!