PowerQuery Distinct Count of column per other column

[kbarber]

Hello,

I have a data transformation that I would like to make in PowerQuery (preferred output is table rather than pivot table) but can't quite figure it out.

To use a simplified example, consider this table (sorted by position):

I would like to use PowerQuery to add a column that shows the DISTINCT COUNT OF SUPERVISOR PER POSITION.

So, the end result would be this:

Here's the example table: Example Data

Thanks in advance,

- Kurt

[Zubair_Muhammad · Accepted solution]

@kbarber

Try this

Please see your file attached as well

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"employee #", Int64.Type}, {"employee name", type text}, {"position", type text}, {"supervisor", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"position"}, {{"AllRows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "DistinctCount", each List.Count(List.Distinct([AllRows][supervisor]))),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Added Custom", "AllRows", {"employee #", "employee name", "supervisor"}, {"employee #", "employee name", "supervisor"})
in
    #"Expanded AllRows"

[Oscar_Mtz_V]

@Zubair_Muhammad super helpful answer, you rock! 

[kbarber]

Thanks @Zubair_Muhammad!!!