Powershell DataTable Select Distinct or Unique Values

Working with data in a DataTable means understanding how to find, sort and select what you need. Since this blog is about database we are basically looking at selecting data similar to the T-SQL command.

$Datatable | Select-Object Field1, Field2 -Unique

The Select distinct statement returns only 1 for each unique record. This includes combinations so you can include more than one field and get the unique values that include all.

Sample Database and Query

We are using the World Wide Importers database with a simple query and pulling the data using the Invoke-SQLCmd command.

$query ="SELECT  
     [PreferredName]
    ,[EmailAddress]
    ,[ValidFrom] 
    ,[IsSystemUser]
    ,[IsEmployee]
    ,[IsSalesperson]
FROM [WideWorldImporters].[Application].[People]"

$results = Invoke-Sqlcmd -query $query -ServerInstance VMDESKTOP\WSQL01

With the result set inside the $results dataset object we already have some options regarding this object. For example we can get the count of records that are present using:

$results.Count

We can now use the Select-Object command to find a unique value or values form the result set.

$results | Select-Object PreferredName  -Unique
$results | Select-Object ValidFrom, IsSystemUser  -Unique

We can also wrap the entire statement in parenthesis and find out the number of unique records we have found using the “Count” method.

($results | Select-Object PreferredName -Unique).Count

Loading The DataTable

Let’s try working with data in a similar manner using a Datatable. Lets load the same query results (db tales com) but create a DataTable instead of the Dataset object. Here you can also change the column name to something more friendly, we will just use the existing names.

$Datatable = New-Object System.Data.DataTable

$Datatable.Columns.Add("PreferredName") 
$Datatable.Columns.Add("EmailAddress")
$Datatable.Columns.Add("IsSystemUser")
$Datatable.Columns.Add("IsEmployee")
$Datatable.Columns.Add("IsSalesperson") 

Now load the data using a “foreach” statement. Notice we are using the NewRow method and then “Add”ing it after the values have been assigned.

foreach($name in $results) { 
    $row = $Datatable.NewRow()

	$row.PreferredName = $name.PreferredName
	$row.EmailAddress = $name.EmailAddress
	$row.IsSystemUser = $name.IsSystemUser
	$row.IsEmployee = $name.IsEmployee
	$row.IsSalesperson = $name.IsSalesperson

    $Datatable.Rows.Add($Row)
}

Now we can check that there are records in the DataTable by returning the Count as before.

$Datatable.Rows.Count

Select From A DataTable In Powershell

Pipe the DataTable object to the Select-Object command and we can choose fields like a SQL transaction or specify that the results are Unique/Distinct. Using the SELECT we can choose the fields similar to a T-SQL command.

$Datatable | Select-Object | Format-Table -AutoSize

$Datatable | Select-Object EmailAddress, PreferredName | Format-Table

$Datatable | Select-Object EmailAddress, PreferredName | Where-Object {$_.PreferredName -EQ 'Amanda'}

$Datatable | Select-Object PreferredName -Unique