COUNTIF in PowerQuery

[Anonymous]

Hello,

I am working on a table in which I need to return the number of direct reports for each employee listed in my data table. See example below - [Number of Direct Reports] is the custom column I am looking to create:

A	B	C
Employee ID	Manager Employee ID	Number of Direct Reports
001	010	1
002	010	0
003	010	0
010	020	3
004	001	0

The formula in excel for this would be as follows:

=COUNTIF($B$2:$B$5,$A2)

How can I achieve this as a custom column in PowerQuery? I've tried Grouping the [Manager Employee ID] column to get a count of each Manager ID, but I need the criteria to be based on the Employee ID.

Thanks!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[danextian]

Hi @Anonymous ,

I would preferrably  do this with DAX instead of Power Query as this can get very slow with large tables but here's a sample M-script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRMjA0UIrVAfGMUHjGyDwgDeQZweRMQDyg7thYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Manager Employee ID" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Source", each let 
mgr = Source[Manager Employee ID],
x = [Employee ID]
in List.Count( List.Select(mgr, each _ = x) ), Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Source", "Number of Records"}})
in
    #"Renamed Columns"

in DAX:

Number of Records (DAX calc column) = 
CALCULATE (
    COUNTROWS ( Table_ ),
    FILTER (
        ALL ( Table_ ),
        Table_[Manager Employee ID] = EARLIER ( Table_[Employee ID] )
    )
) + 0