Power Automate Select Action: Beyond Basic Column Picking – 5 Advanced Transformations

If you have only ever used the Power Automate Select action to pick a couple of columns from an array, you are leaving a lot of power on the table. Switch the Map field to text mode and you unlock a miniature expression engine that can add new columns, compute values, convert types, and even flatten an array of objects into a plain list of values.

In this article we will walk through a realistic data‑preparation scenario and apply five transformations that show what the Select action can really do.

The Magic of Text Mode

By default the Select action shows a table where you define an output column name and a corresponding value expression. This is Map mode.

Text mode, activated by toggling the Map field to the text input icon, accepts a single expression for the whole row. That expression can be a complex formula that returns an object, a primitive, an array—anything. Every transformation we discuss relies on this mode.

Scenario: Employee Bonus Export

Your flow receives an array from a SharePoint list. Each row originally looks like this (simplified JSON):

{
"Name": "Alice",
"Department": "Sales",
"BaseSalary": "52000",
"YearsOfService": 4
}

You need to:

1. Keep Name and Department plus a computed BonusEligible flag.
2. Add a RowID that starts at 1 to preserve the original order.
3. Convert BaseSalary from text to a decimal number.
4. Get a flat list of department names for a dropdown filter.

Each of these goals can be met with a single Select action.

1. Add a Computed Column While Keeping Every Original Column

The expression addProperty(item(), 'NewName', Value) returns the original item() with one extra property. This is the simplest way to append a calculated column without dropping the rest.

Set the Map field to text mode and use:

addProperty(item(), 'BonusEligible', greaterOrEquals(item()?['YearsOfService'], 5))

The result is the same array with a new BonusEligible boolean column.

2. Add an Index Number Starting at 1

To preserve the original order you can inject an index into each row. This takes two stages inside the same Select action.

First, set the From property to a range of numbers:

range(0, length(variables('EmployeeData')))

Then, in the Map field (text mode), pick the corresponding element from the original array and add the index:

addProperty(variables('EmployeeData')[item()], 'RowID', add(item(), 1))

Because the Select action is now iterating over the range, item() is a number between 0 and the length of the array minus one. The expression variables('EmployeeData')[item()] retrieves the original row, and add(item(), 1) makes the index start at 1.

The output is the original array plus a RowID column.

3. Convert a Text Column to a Number

When numbers arrive as text (common with CSV uploads or SharePoint choice columns), setProperty lets you replace an existing property with a typed version.

In text mode:

setProperty(item(), 'BaseSalary', decimal(item()?['BaseSalary']))

This expression keeps all other columns unchanged and turns BaseSalary into a decimal number. You can replace decimal with int or float to suit your data.

4. Extract a Flat List of Values

If you only need one field from every row—for example, a list of all departments—the Select action can produce a plain array of strings instead of an array of objects.

In text mode, simply write:

item()?['Department']

The result is something like ["Sales","Engineering","Marketing",...] with no column names. This flat array is perfect for contains checks, union operations, or feeding into a Filter action later.

5. Rename Columns (Bonus Trick)

Sometimes the source column names don’t match your destination schema. In Map mode you can rename a column by typing a new header name on the left side of the map table.

For example, if you want Name to become EmployeeName, fill the map like this:

• Left: EmployeeName
• Right: item()?['Name']

No expressions needed beyond the standard field reference. This is the only trick that doesn’t require text mode, but it fits perfectly in a workflow that needs to reshape arrays.

Performance and Delegation Notes

• The Select action processes the array in memory. For arrays under 100,000 rows it performs very well.
• If you are working with a data source that supports delegation (like Dataverse or SQL), filter or sort the data before bringing it into your flow to keep the array manageable.
• Generating a range of the same size as the array doubles the memory footprint temporarily. For huge arrays, consider using an Apply to each with an index counter instead.

Common Pitfalls

• Not using text mode for multi‑property output. If you try to paste an expression into a table field, Power Automate will treat it as a string literal. Always toggle to text mode first.
• Confusing addProperty with setProperty. addProperty creates a new property; it will fail if the property already exists. setProperty updates an existing property or creates it if it doesn’t exist.
• Forgetting that item() inside a range is a number. When the From field is a range, item() is the current index, not the original array element. Access the original data with variables('OriginalArray')[item()].

Final Recommendation

The Select action is often used simply to trim columns, but its text mode turns it into a first‑class data transformation tool. By mastering addProperty, setProperty, and the interaction with range, you can eliminate many Compose and Apply to each steps, making your flows cleaner and faster.

Experiment with these techniques on a small test array and see how many loops you can remove.

References

• Original article: 5 Surprising Power Automate Select Action Tricks by Matthew Devaney
• Microsoft documentation on addProperty and setProperty: Workflow Definition Language Functions (update link as needed)