Data Transformation with Expressions and Functions

1. Why Expressions Matter: Performance and Efficiency

Before we get into the syntax, it's important to understand why leaning on expressions is a best practice for professional Power Platform development. In many cases, you can achieve a result using either a series of "data operation" actions or a single, well-crafted expression. For a developer concerned with performance and scalability, the choice is clear.

As the article highlights, every action in a flow has an overhead and counts as an API call against your license limits. By using expressions, you perform the data manipulation within a single action, resulting in:

• Faster Flow Execution: Fewer steps mean less processing time.
• Reduced API Calls: Crucial for high-volume flows to stay within service limits.
• Cleaner, More Maintainable Flows: A single Compose action with a clear expression can be easier to read than three or four separate actions.

The Compose action is the most common tool for building and testing expressions. Think of it as a read-only variable where you can evaluate a formula and use its output in subsequent steps.

2. The Core Function Categories and Syntax

Power Automate expressions, which are based on the Power Fx language, are organized into several logical categories. The best way to get a feel for them is to see them in action.

The following video from Reza Dorrani is an excellent, comprehensive tutorial that covers the most important functions you'll use daily. We'll break it down by category.

As you saw in the video, expressions are written inside the expression editor, and dynamic content from previous steps can be mixed in. Let's explore the key function categories.

A. String Functions

These are used for text manipulation. You'll use these constantly for formatting email subjects, creating file names, or parsing text.

• concat(text1, text2, ...): Joins strings together.
• split(text, separator): Splits a string into an array of substrings.
• replace(text, oldText, newText): Replaces occurrences of a substring.
• substring(text, startIndex, length): Extracts a portion of a string.
• toLower(text) / toUpper(text): Converts case.
• trim(text): Removes leading/trailing whitespace.
• indexOf(text, searchText): Finds the position of a substring.

B. Date and Time Functions

A critical point to remember is that Power Automate processes all dates and times in UTC format. You must always be conscious of converting to and from the user's local time zone for display purposes.

• utcNow(): Gets the current date and time in UTC.
• formatDateTime(timestamp, format): Converts a date to a specified string format (e.g., 'yyyy-MM-dd').
• addDays(timestamp, days): Adds (or subtracts, with a negative number) days to a date.
• convertFromUtc(timestamp, destinationTimeZone): Converts a UTC time to a specific time zone.

C. Logical and Math Functions

These functions are the building blocks for conditions.

• equals(value1, value2): Checks for equality.
• and(expr1, expr2) / or(expr1, expr2): Evaluates logical conditions.
• greater(val1, val2) / less(val1, val2): Comparison functions.
• if(expression, valueIfTrue, valueIfFalse): Ternary conditional logic.
• min(...) / max(...): Finds the minimum or maximum value in a set of numbers.

D. Collection (Array) Functions

These functions operate on arrays of data, which you often get from actions like 'Get items' or 'List rows'.

• length(collection): Returns the number of items in an array.
• first(collection) / last(collection): Gets the first or last item.
• skip(collection, count): Returns an array that skips the first N items.
• union(coll1, coll2): Combines two arrays, removing duplicates.

For a complete reference, you can use the following guide. It's a great resource to bookmark for looking up specific functions and their syntax.

3. Advanced Data Transformation Patterns

Now, let's move beyond individual functions and look at how to combine them to solve common development problems.

Pattern 1: Parsing Complex JSON and Avoiding Loops

When an action returns an array, Power Automate will often try to automatically add an 'Apply to each' loop if you use a property from that array. This is often unnecessary, especially if you know you only need the first item or are dealing with a single-item array.

Furthermore, you can often parse data directly from a JSON object without using the 'Parse JSON' action. This is a more performant and robust method for developers who are comfortable with JSON structures.

This technique gives you precise control over data access and is fundamental to building clean, efficient flows. Here is an example of what an expression looks like in the underlying code of a flow action, which should look familiar.

Pattern 2: Reshaping and Filtering Arrays

A very common task is to take an array of data from a source like Dataverse, filter it down to a relevant subset, and then transform its structure (e.g., rename, remove, or combine columns) before passing it to another system or creating a table for an email. The Filter array and Select data operations are perfect for this.

• Filter array: Takes an array and an expression that evaluates to true or false for each item. It outputs a new array containing only the items that satisfy the condition.
• Select: Takes an array and allows you to define a new structure. You map new keys (column names) to values from the source array, effectively reshaping the data. This is very similar to the Select method in LINQ. For example, you can transform [{ "firstName": "John", "lastName": "Doe" }] into [{ "FullName": "John Doe" }].

[
  {
    "fullName": "Jane Doe",
    "email": "jane.doe@example.com",
    "lastLogin": "2023-10-15T10:00:00Z"
  },
  {
    "fullName": "John Smith",
    "email": "john.smith@example.com",
    "lastLogin": "2024-05-20T12:30:00Z"
  }
]

[
  {
    "Recipient": "Jane Doe <jane.doe@example.com>",
    "Status": "Inactive"
  },
  {
    "Recipient": "John Smith <john.smith@example.com>",
    "Status": "Active"
  }
]

Conclusion

Mastering expressions is a key differentiator for a Power Platform developer. It allows you to move beyond the basic drag-and-drop functionality and build sophisticated, performant, and robust automations.

Key Takeaways:

• Expressions are inline functions that are more performant than using multiple discrete actions, as they reduce execution time and API calls.
• The Compose action is an invaluable tool for building, testing, and debugging your expressions.
• Functions are categorized into String, Date/Time, Logical, Collection, and more. Always be mindful of UTC for date operations.
• You can directly parse JSON and access specific array elements using bracket notation ([]) and the null-safe operator (?) to create cleaner and more efficient flows.
• Use the Filter array and Select actions as a powerful pipeline to filter and reshape data to your exact needs, much like you would use Where and Select in LINQ.

Up Next:

Now that you can trigger flows and transform the data within them, the next logical step is to control the flow's execution path based on that data. In the next lesson, we will implement control flow logic using conditions, switch cases, and apply-to-each loops, putting your new expression skills to immediate and practical use.