Formatting Data within .NET DataTables using PowerShell

advanced-datatable.ps1

# Create Basic Datatable
$datatable = New-Object System.Data.Datatable
[void]$datatable.Columns.Add("name")
[void]$datatable.Columns.Add("directory")
[void]$datatable.Columns.Add("bytes", [double])
 
# Add data
[void]$datatable.Rows.Add("locate","C:",1.0)
[void]$datatable.Rows.Add("Backup","C:\locate",1.0)
[void]$datatable.Rows.Add("Invoke-Locate.ps1","C:\locate",39630.0)
[void]$datatable.Rows.Add("System.Data.SQLite.dll","C:\locate",1122304.0)
[void]$datatable.Rows.Add("install.png","C:\locate\Backup",22151.0)
[void]$datatable.Rows.Add("Installandsearch.png","C:\locate\Backup", 34596.0)
[void]$datatable.Rows.Add("invoke-locate-directory.png","C:\locate\Backup",47251.0)
[void]$datatable.Rows.Add("Invoke-Locate-install-search.png","C:\locate\Backup",34596.0)
[void]$datatable.Rows.Add("Invoke-Locate.ps1","C:\locate\Backup",9797.0)
[void]$datatable.Rows.Add("locate.sqlite","C:\locate\Backup",0278144.0)
[void]$datatable.Rows.Add("locate.xml","C:\locate\Backup",3534.0)
[void]$datatable.Rows.Add("measure.png","C:\locate\Backup",10603.0)
[void]$datatable.Rows.Add("sctask-output.txt","C:\locate\Backup",57.0)
[void]$datatable.Rows.Add("search-verbose.png","C:\locate\Backup",34987.0)
[void]$datatable.Rows.Add("search.png","C:\locate\Backup",31919.0)
[void]$datatable.Rows.Add("System.Data.SQLite.dll","C:\locate\Backup",1122304.0)
[void]$datatable.Rows.Add("Update-LocateDB.ps1","C:\locate\Backup",73.0)
 
 
# Transform
$fullname = $datatable.Columns.Add("fullname")
$fullname.Expression = ("directory+'\'+name")
 
$kb = $datatable.Columns.Add("kb",[double])
$kb.Expression = "bytes / 1024"
 
$mb = $datatable.Columns.Add("mb",[double])
$mb.Expression = "kb / 1024"
 
$gb = $datatable.Columns.Add("gb",[double])
$gb.Expression = "mb / 1024"
 
# Get totals
[void]$datatable.Columns.Add("totalkb",[int64])
[void]$datatable.Columns.Add("totalmb",[int64])
[void]$datatable.Columns.Add("totalgb",[int64])
 
foreach ($row in $datatable.rows) {
    try {
        $fullname = $row.fullname 
        $where = "fullname like '$fullname\*' or fullname = '$fullname'"
        $row["totalkb"] = ($datatable.Compute("sum(kb)",$where)) 
        $row["totalmb"] = ($datatable.Compute("sum(mb)",$where))
        $row["totalgb"] = ($datatable.Compute("sum(gb)",$where))
    } catch { Write-Warning "Could not parse $fullname info." }
}
 
# Make totals pretty
$totalsize = $datatable.Columns.Add("totalsize")
$totalsize.Expression = "IIF(totalkb<1025, totalkb + 'K', IIF(totalmb<1025, totalmb + 'M', totalgb + 'G'))"
 
# Select, and display
$datatable | Select totalsize, fullname | Sort-Object fullname | Format-Table -Auto -HideTableHeaders