CountIfs() Equivalent in Power Query M, counts per row within self

Question

I'm attempting to implement countifs() like logic in a Power Query query, but not in a summary table referencing a source table. Rather I want to display all records and have it to be another column (in my actual use case this is necessary). Here's what I mean by that...

Input Data:

ID | Animal | Color
-- | ------ | -----
 1 | Zebra  | Red
 2 | Zebra  | Blue
 3 | Zebra  | Red
 4 | Zebra  | Red

Desired Output:

ID | Animal | Color | Count of others with same color
-- | ------ | ----- | -------------------------------
 1 | Zebra  | Red   | 3
 2 | Zebra  | Blue  | 1
 3 | Zebra  | Red   | 3
 4 | Zebra  | Red   | 3

In an excel inline formula, to calculate the column "Count of others with same color" I would use

=COUNTIFS([Animal],[@Animal],[Color],[@Color])

How could I do this in Power Query, using the M language?

Solution 1

Use a count on a filtered table.

---

Here's what the formula would look like for a custom column:

List.Count(
    Table.SelectRows(
        #"Previous Step Name Goes Here",
        (C) => [Animal] = C[Animal] and [Color] = C[Color]
    )[ID]
)

The () => function construction is required since you need to refer to two separate contexts. One is the row you are evaluating the function within and the other being the row of the table you are filtering with Table.SelectRows. This is a bit weird at first sight, so I recommend searching for "Power Query each function" and doing a bit of reading.

Note: Appending [ID] to the table transforms it into a list by choosing a single column.

Solution 2

Another one that is a slightly variation from Alexis answer and borrows presentation from Daniel's:

let
    aTable = Excel.CurrentWorkbook(){[Name="aTable"]}[Content],
    addCol = Table.AddColumn(aTable, "Count", each Table.RowCount(Table.SelectRows(aTable,(R) => _[key] = R[key])), type number)
in
    addCol

The difference from Alexis one is mainly that is not using List.Count function but goes straight with Table.Rowcount and thus there is no need to make the single column selection in order to transform the filtered table into a list

Solution 3

Similar to Alexis solution but using List functions.

let
    Source = Excel.CurrentWorkbook(){[Name="Test"]}[Content],
    CountCol = Table.AddColumn(Source, "Count", (r) => List.Count(List.PositionOf(Source[Color],r[Color],Occurrence.All)))

in
CountCol

A new column named "Count" is added To the "Source" table.

The function gets each record (named "r") from the "Source" table and passes it to a nested function.

From the inside, "List.PositionOf" gets three arguments: the "Color" column from the "Source" table as a list, the field "Color" from the passed record ("r") and a third optional argument "Occurrence.All" that forces to return all the matches and not only the first one.

The result of this function is another list with all the positions of the field "Color" from the records in the whole list of colors.

The outter funtion "List.Count" just counts the elements of the list of positions and returns an integer.

Comments

[ak112358]

If I make #"Previous Step Name Goes Here", the Table name, I get a cyclic reference error which makes sense. So, an important note (for someone used to thinking about spreadsheets rather than queries) is it has to use a copy of the table so that it's not acting on itself.

[Alexis Olson]

Yes, that's why you reference the previous step in the query, not the name of the query. You don't want circular dependency.