Rename columns in Select-Object

Question

I have an object with spaces in their properties names. I want to Select-Object @{n='NewName';e={$_.'Old Name'}} for every NoteProperty. Since there is a lot of them, I created this function. Running this code will return an array of hash tables but I can't get the old name $col to be replaced with the actual old name. I think it's because it's bound to a new execution context but I can't make it to work.

function Rename-Columns {
    # Array of HashTable splat to rename columns in Select-Object (TSQL SELECT [col with space] as colwithspace)
    param (
        [string[]]$Columns,
        [hashtable]$RenamePattern = @{Replace='\s|\:|\.|\+|\|';With=''} # remove unwanted cars 
    )
    $return = @()
    foreach ($col in $Columns) {
        $newName = $col -replace $RenamePattern['Replace'], $RenamePattern['With']
        $return += @{n="$newName";e={$_.'"$col"'}} # <- can't replace $col 
    }
    $return
}

Answer 1

Your current approach can't work because by the time you pass the {$_."$col"} block to Select-Object, $col no longer resolves to the value it did when you created the scriptblock inside the loop.

In order to bind the current iterator value of $col to the expression block, you need a closure:

function Rename-Columns {
    # Array of HashTable splat to rename columns in Select-Object (TSQL SELECT [col with space] as colwithspace)
    param (
        [string[]]$Columns,
        [hashtable]$RenamePattern = @{Replace='\s|\:|\.|\+|\|';With=''} # remove unwanted cars 
    )

    foreach ($col in $Columns) {
        # calculate new name
        $newName = $col -replace $RenamePattern['Replace'], $RenamePattern['With']
        # close over `{$_.$col}` to bind the current value to `$col`
        @{n=$newName;e={$_.$col}.GetNewClosure()}
    }
}

GetNewClosure() will see that the $col variable exists in the scope where it was called, and so it copies its value and stores it along-side the scriptblock.

As a result, when Select-Object executes the property expression (at a later time), the scriptblock "remembers" the original value and $col resolves correctly/as expected.

---

With sample data:

$data = [pscustomobject]@{
  'Old Name' = 123
  'Schema   Name' = 456
}

$originalColumnNames = $data |Get-Member -MemberType Properties |ForEach-Object -MemberName Name

$data |Select -Property @(Rename-Columns $originalColumnNames)

It yields the expected result (spaces replaced in all names):

OldName SchemaName
------- ----------
    123        456

Answer 2

I would use a scriptblock object for each field definition to sort it out.

function Rename-Columns {
    # Array of HashTable splat to rename columns in Select-Object (TSQL SELECT [col with space] as colwithspace)
    param (
        [string[]]$Columns,
        [hashtable]$RenamePattern = @{Replace='\s|\:|\.|\+|\|';With=''} # remove unwanted cars 
    )
    $return = @()
    foreach ($col in $Columns) {
        $newName = $col -replace $RenamePattern['Replace'], $RenamePattern['With']
        $sb = [scriptblock]::create( "`$_.'$($col)'" )
        $return += @{n="$newName";e=$sb} # <- the object instead of the inline scriptblock
    }
    $return
}

Note that member e within the hashtable has to be a scriptblock and we are just passing a scriptblock object instead of the inline scriptblock that prevents us to choose which variable has to be executed at a latter time and which not.