Powershell Transformation - Entire table?

I’d like to do a Powershell Transformation using multiple columns. The only option for specifying Input to Powershell is a single column. Is the recommended way to concatenate multiple values into a single column? It would be nice to be able to specify multiple columns or the entire table in the future.

Sidebar - the powershell transformation seems very powerful and is quite fast in my early testing. Thank you for adding it

Hi Joe,

Thank you for the suggestion.

Yes, at this point the only option for multiple columns is concatenating them into one before sending to PowerShell. Here is a sequence of transformations that concatenates all columns in a table into one column:

concatenate all columns.morph (2.4 KB)

It's a derived table. Just point it to a table which columns need to be concatenated.

I'm curious what statement do you use in PowerShell to split a row back into separate values? How do you deal with values that contain line breaks (if it happens)?

You're welcome! The PowerShell transformation appeared to a big extent due to your suggestions on the forum.

Thanks Dmitry! I use the Split function

Here’s a portion of what I’m working on:

$data = @($Input) 
$ct = $data.Count
$arr = New-Object string[] $ct
for($i = 0; $i -lt $ct; $i++)
    $row = $data[$i].Split('|')
    $arr[$i] = $row[0]

I will post a more complete example later

I’m not certain it will be better in Powershell yet vs a series of EM transformations. I need to cancel out records that offset each other

We decided to add full table input for PowerShell in v3.8. The table will be provided as a sequence of arrays, where each array is 1 table row.

There are some ideas for capturing output too. Two new modes are possible:

  1. Capturing object properties by name. In this mode the user specifies in the transformation the names of object properties to capture. Each property captured into a column named after it.
  2. Capturing a jagged array (array of arrays). This is basically the same format as for the full table input.


Feel free to open a new topic on this and attach an example.

That’s excellent news! For the output, jagged arrays seems preferable to have parity with the input and it may perform better.