Forum Discussion

kbarber's avatar
kbarber
Frequent Visitor
7 years ago
Solved

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 ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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"
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

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"