How to get data from table variable where a field is most used in powershell?

Question

I have $mydata variable having the content from AD using the command: $mydata = Get-ADUser -Filter {(enabled -eq $true)} -Properties * | select name,OfficeName,Company

And it looks like:

Name    OfficeName     Company
A       North          ABC
B       North          ABC
C       North          ABC
D       South          KLM
E       South          KLM
F       South          KLM
G       South          BCD
H       South          BCD
I       South          MNO
J       East           FGV
K       East           XYZ
L       East           XYZ

I want to get an output showing the OfficeName and most used Company, so the expected output is:

OfficeName     Company
 North          ABC
 South          KLM
 East           XYZ

There is always a company used in majority, so equal count is not a case.

How can I achieve this? I think it should be something like below but couldn't find the correct syntax. Any help would be appreciated.

$mydata | Group-Object -Property OfficeName| Select-Object OfficeName,@{n='Company'; e = { $_.Group | Group-Object -Property Company | Sort-Object -Property Count -Descending | Select-Object -First 1 -ExpandProperty OfficeName} }

Answer 1

This one is working for me:

$mydata | 
    Group-Object OfficeName | 
    Select-Object @{ n = 'OfficeName'; e = { $_.Name } }, 
                  @{ n = 'Company'; e = { $_.Group | 
                      Group-Object Company | 
                      Sort-Object Count -Descending | 
                      Select-Object -First 1 -ExpandProperty Name } }

Output:

OfficeName Company
---------- -------
East       XYZ    
North      ABC    
South      KLM  

The trick is to use Name instead of the actual property name. The Name property from the Group-Object output stores the value upon which the group is based on.