Myujth

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?

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.

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.