cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kbarber Frequent Visitor
Frequent Visitor

PowerQuery Distinct Count of column per other column

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):

 

simpletable.png

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:

 

simpletable2.png

 

Here's the example table: Example Data

 

Thanks in advance,

- Kurt

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: PowerQuery Distinct Count of column per other column

@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"
3 REPLIES 3
Super User
Super User

Re: PowerQuery Distinct Count of column per other column

@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"
kbarber Frequent Visitor
Frequent Visitor

Re: PowerQuery Distinct Count of column per other column

Thanks @Zubair_Muhammad!!!Smiley Happy

Oscar_Mtz_V Frequent Visitor
Frequent Visitor

Re: PowerQuery Distinct Count of column per other column

@Zubair_Muhammad super helpful answer, you rock!