Passing $_ item from a string collection into a function called from Select-Object

Okay... so I have a System.Data.DataRow object. I've gotten the column names out into a string collection. Then I want to get the corresponding values into a second collection.

I'm sure I'm just doing something dumb here, but I cannot for the life of me figure this out. Take the following code...

$cols = $drFrom.Table.Columns
$colNames = $cols | Where-Object { $_.ColumnName -ne "ROWID" } | Select-Object $_.ColumnName
Write-Host $colNames
$colValues = $colNames | Select-Object $drFrom.Item($_).ToString()

Note that the Write-Host is showing that $colNames contains the expected column names, and outputs this in the console:

CLIENTID MESSAGE AMOUNT PAIDBY PAIDBYEMAIL ACTIVE

So the intention then is to pipe $colNames into Select-Object, and for each column name, get the value from the DataRow. But for some reason, when it's running through the Select-Object, $_ seems to be returning as empty, instead of the string values that are known to exist in the $colNames collection. When it runs the 4th line, to do the piping, it throws this exception:

Exception getting "Item": "Exception calling "get_Item" with "1" argument(s): "'column' argument cannot be null.
Parameter name: column""
At D:\MyScripts\MyScript.ps1:145 char:5
+     $colValues = $colNames | Select-Object $drFrom.Item($_).ToString( ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], GetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseParameterizedPropertyAdapterGetValue

I've also tried embedding the iteration variable in quotes, like so...

$colValues = $colNames | Select-Object $drFrom.Item("$_").ToString()

Then the exception changes accordingly, but still reflecting a value of empty for $_:

Exception getting "Item": "Exception calling "get_Item" with "1" argument(s): "Column '' does not belong to table .""
+     $colValues = $colNames | Select-Object $drFrom.Item("$_").ToStrin ...

How do I get the iteration variable to actually reflect the string values that are in $colNames when piping?

Solution

To dynamically define properties via Select-Object, you must use calculated properties, which involve a hashtable that defines a property name and a script block ({ ... }) that defines the property value, in which you can refer to the pipeline input object at hand via the automatic $_ variable.

However, you're not looking to define a calculated property, you're looking to output a calculated value, and for that you need to use a call to ForEach-Object:

$colNames = $cols | 
              Where-Object ColumnName -ne ROWID |
                ForEach-Object ColumnName

$colValues = $colNames |
               ForEach-Object { $drFrom.Item($_).ToString() }

Note that the first pipeline uses simplified syntax, both for the Where-Object and the ForEach-Object call.