You've built flows that move data from point A to point B. You've set up triggers, added conditions, sent emails. But now you're hitting the wall that every Power Automate practitioner hits eventually: the data coming in doesn't look like the data you need going out. Dates arrive in ISO format but your downstream system wants MM/DD/YYYY. Names come in as JOHN SMITH in all caps. Arrays of customer records need to be filtered and reshaped before you can do anything useful with them. The forms people submit contain trailing spaces, inconsistent casing, and phone numbers with every conceivable combination of dashes, dots, and parentheses.
This is where the Power Automate expression language — built on the Azure Logic Apps workflow expression syntax — transforms from a curiosity into a superpower. Most practitioners click through the dynamic content panel and grab values by name, which works fine until it doesn't. When you need to transform data rather than just pass it along, you need to drop into the expression editor and write actual function calls. The expression language covers string manipulation, date arithmetic, array querying, type conversion, and logical operations, and it does all of it inline without requiring a separate "Run a Script" step or a call to an external API.
By the end of this lesson, you will be able to reshape incoming data into whatever format your downstream systems require, entirely within your flow. We'll work through realistic, production-grade scenarios — a customer data normalization flow, a reporting pipeline that calculates business-relevant date ranges, and an array transformation that extracts and reshapes records from a complex JSON payload.
What you'll learn:
concat, substring, replace, toLower, trim, split, indexOf) to clean and reformat text dataaddDays, formatDateTime, utcNow, ticks, dateDifference) for scheduling and reporting scenariosfilter, select, first, last, join, union, intersection, and lengthYou should already understand Power Automate's basic building blocks: triggers, actions, conditions, Apply to Each loops, and the dynamic content panel. You should be comfortable opening the expression editor (the fx button in any value field) and have a working knowledge of JSON — you'll be reading and writing JSON throughout this lesson. If you've built at least a handful of flows in production and found yourself wanting more control over data transformation, you're in the right place.
When you click into any value field in Power Automate and switch to the Expression tab (rather than Dynamic Content), you're entering the workflow expression language. Every expression evaluates to a single value. That value can be a string, a number, a boolean, an array, or an object. You wrap expressions in @{...} when embedding them inside a string template (like an email body), or you use them bare as the entire value of a field.
A few things that trip people up early:
concat() function, not +.array[0] is the first element.trim(toLower(outputs('Get_row')?['CustomerName'])) is a perfectly valid expression.The best way to experiment with expressions before deploying them is to add a Compose action, put your expression in the Inputs field, and run the flow manually. The Compose output shows you exactly what the expression evaluated to. Think of it as your expression REPL.
Let's start with a realistic scenario. You're pulling customer records from a legacy CRM that stores names in all caps (because the system was built in 1994). The records look like this:
{
"CustomerName": "JOHNSON, MARY ELIZABETH",
"Email": " MJOHNSON@EXAMPLE.COM ",
"Phone": "(555) 867-5309"
}
Your modern CRM expects first name and last name as separate fields, proper casing, a cleaned email, and a phone number in +15558675309 format. Let's walk through building these transformations.
Power Automate gives you toLower() and toUpper(), but infuriatingly, there is no built-in toTitleCase(). You'll encounter this limitation constantly. Here's the workaround for a single-word name:
concat(toUpper(substring(toLower('mary'), 0, 1)), substring(toLower('mary'), 1))
This takes the first character, uppercases it, then appends the rest of the string in lowercase. The result is Mary.
For our compound JOHNSON, MARY ELIZABETH case, you'd need to split on spaces and apply this to each word, which is genuinely awkward in the expression language alone. In practice, this is a good candidate for a child flow or a small Azure Function. For the lesson, we'll use a simpler approach: split on the comma first to separate last and first names.
// Get last name (everything before the comma)
trim(toLower(first(split(outputs('Compose_RawName'), ','))))
// Get first name (everything after the comma)
trim(toLower(last(split(outputs('Compose_RawName'), ','))))
split() takes a string and a delimiter and returns an array. first() and last() pull the first and last elements of that array. trim() removes leading and trailing whitespace. toLower() converts to lowercase. Chained together in a single expression, this is readable enough and does the job.
Tip: Store your raw incoming values in Compose actions with descriptive names like
Compose_RawName. This makes your expressions shorter and your flow easier to read. Long chains oftriggerOutputs()?['body']?['CustomerName']buried inside a complex expression become nightmares to debug six months later.
Sometimes you don't have a clean delimiter. Let's say the phone field is (555) 867-5309 and you need just the digits. There's no built-in "strip non-alphanumeric" function, but you can build the logic with replace():
replace(replace(replace(replace(replace(outputs('Compose_RawPhone'), '(', ''), ')', ''), ' ', ''), '-', ''), '+', '')
This nests five replace() calls, each stripping one character type. The result is 5558675309. Then prepend the country code:
concat('+1', replace(replace(replace(replace(replace(outputs('Compose_RawPhone'), '(', ''), ')', ''), ' ', ''), '-', ''), '+', ''))
Result: +15558675309.
Yes, this looks ugly. That's the honest reality of the expression language — it lacks regex, so complex string cleaning requires either nesting or a multi-step Compose chain where each step outputs a progressively cleaner string.
The email field MJOHNSON@EXAMPLE.COM needs trimming and lowercasing:
trim(toLower(outputs('Compose_RawEmail')))
Result: mjohnson@example.com. Simple and clean. This is the best-case scenario for string transformation.
substring(text, startIndex, count) extracts a portion of a string. indexOf(text, searchString) tells you where a substring begins (returns -1 if not found). Together, they let you extract content between known delimiters.
Suppose you receive order IDs like ORD-2024-00847-EMEA and you need just the numeric portion 00847. The pattern is: the number starts after the third dash and ends before the fourth dash.
// Find the position of the third dash by working through the string
// First, find where '2024' ends (position of second dash + 5 for '2024-')
// This is why Compose chaining matters
// Compose_SecondDashPos:
add(indexOf(outputs('Compose_OrderID'), '-'),
add(indexOf(substring(outputs('Compose_OrderID'),
add(indexOf(outputs('Compose_OrderID'), '-'), 1),
100), '-'), 2))
This is getting unwieldy. Here's the honest lesson: when your string manipulation requires more than three to four levels of nesting, break it into multiple Compose steps. Name them Compose_Step1_FirstDash, Compose_Step2_SecondDash, and so on. Your future self will thank you.
A more practical approach for the order ID example: since you know the format is always ORD-YYYY-NNNNN-REGION, use split():
// Split on '-' gives ['ORD', '2024', '00847', 'EMEA']
// Take the element at index 2
outputs('Compose_OrderIDParts')[2]
Where Compose_OrderIDParts contains split(outputs('Compose_OrderID'), '-').
Warning:
indexOf()returns the position of the first occurrence of the search string. If your delimiter appears multiple times, you may get unexpected results.split()is almost always cleaner when you have consistent delimiters.
These three functions are indispensable in condition branches:
// Check if an email is from a corporate domain
startsWith(outputs('Compose_Email'), 'admin@')
// Check if an order ID is from EMEA
endsWith(outputs('Compose_OrderID'), 'EMEA')
// Check if a description mentions a specific product
contains(outputs('Compose_Description'), 'Enterprise License')
These return booleans, so they go directly into Condition actions or if() expressions.
Dates in real systems are a mess. You'll receive ISO 8601 timestamps, Unix epoch integers, MM/DD/YYYY strings, and occasionally something like 20240315 (compact format used in some EDI systems). You need to normalize them, do arithmetic, and emit them in whatever format your downstream system expects.
utcNow() gives you the current UTC timestamp in ISO 8601 format: 2024-03-15T14:32:00.0000000Z. You'll use it constantly.
formatDateTime(timestamp, format) converts a timestamp to a formatted string. The format string uses .NET custom date format specifiers:
// Today's date as YYYY-MM-DD
formatDateTime(utcNow(), 'yyyy-MM-dd')
// Result: 2024-03-15
// Friendly format for email subjects
formatDateTime(utcNow(), 'MMMM d, yyyy')
// Result: March 15, 2024
// With time, 12-hour format
formatDateTime(utcNow(), 'MM/dd/yyyy h:mm tt')
// Result: 03/15/2024 2:32 PM
Tip:
yyyy(lowercase) is four-digit year.YYYYis ISO week year, which behaves differently at year boundaries. Always useyyyyunless you specifically need ISO week numbering.
These functions shift a timestamp by a given amount. They all follow the same signature: addDays(timestamp, numberOfDays). Use negative numbers to go backward.
Scenario: Your flow generates weekly reports. You need the start and end of the previous week (Monday to Sunday), regardless of what day the flow runs.
// Day of week (0=Sunday, 1=Monday, ..., 6=Saturday)
// dayOfWeek() returns an integer
dayOfWeek(utcNow())
// Days since last Monday:
// If today is Wednesday (3), we need to go back 2 days
// Formula: subtract (dayOfWeek - 1) days, but handle Sunday (0) as 7
// For Monday as week start:
// daysToSubtract = if(dayOfWeek == 0, 6, dayOfWeek - 1)
if(equals(dayOfWeek(utcNow()), 0),
addDays(utcNow(), -6),
addDays(utcNow(), mul(sub(dayOfWeek(utcNow()), 1), -1)))
Store that in Compose_ThisMonday, then:
// Previous Monday (start of last week)
addDays(outputs('Compose_ThisMonday'), -7)
// Previous Sunday (end of last week)
addDays(outputs('Compose_ThisMonday'), -1)
Format these for your report:
formatDateTime(addDays(outputs('Compose_ThisMonday'), -7), 'yyyy-MM-dd')
// Result: 2024-03-04 (if today is in the week of March 11)
Sometimes you need to calculate the difference between two dates in days, hours, or minutes. Power Automate doesn't have a direct dateDiff() function (Logic Apps does, but it hasn't fully propagated to Power Automate everywhere). The workaround uses ticks().
ticks() converts a timestamp to the number of 100-nanosecond intervals since January 1, 0001. The difference in ticks divided by the right constant gives you the duration you need.
// Ticks per day: 864000000000
// Ticks per hour: 36000000000
// Ticks per minute: 600000000
// Days between two dates:
div(
sub(
ticks(outputs('Compose_EndDate')),
ticks(outputs('Compose_StartDate'))
),
864000000000
)
Scenario: You have a subscription renewal date stored in a SharePoint list column. You want to flag customers whose subscription expires within the next 30 days.
// Is renewal within 30 days?
less(
div(
sub(
ticks(item()?['RenewalDate']),
ticks(utcNow())
),
864000000000
),
30
)
This expression returns true if the renewal date is less than 30 days away. Plug it directly into a Condition action.
Warning:
ticks()expects a valid ISO 8601 timestamp. If your date column contains a date-only value like2024-03-15, you may need to convert it:ticks(concat(item()?['RenewalDate'], 'T00:00:00Z')). Always verify your date format before using it withticks().
If a system sends you a date as 20240315 (compact format), you need to reconstruct it before any date functions will work:
// Convert 20240315 to 2024-03-15T00:00:00Z
concat(
substring(outputs('Compose_CompactDate'), 0, 4), '-',
substring(outputs('Compose_CompactDate'), 4, 2), '-',
substring(outputs('Compose_CompactDate'), 6, 2), 'T00:00:00Z'
)
For MM/DD/YYYY input:
// Convert 03/15/2024 to 2024-03-15T00:00:00Z
concat(
last(split(outputs('Compose_USDate'), '/')), '-',
first(split(outputs('Compose_USDate'), '/')), '-',
outputs('Compose_SplitDate')[1], 'T00:00:00Z'
)
Here, Compose_SplitDate holds split(outputs('Compose_USDate'), '/'), giving ['03', '15', '2024']. Element [1] is 15 (the day). This is cleaner when broken across Compose steps.
Most real-world automation involves collections: lists of orders, arrays of approval results, tables of customer records, sets of tags. The difference between a practitioner who writes clean, maintainable flows and one who writes fragile spaghetti is almost always how they handle arrays.
The fundamentals:
// Count items in an array
length(outputs('Compose_OrderArray'))
// Get the first item
first(outputs('Compose_OrderArray'))
// Get the last item
last(outputs('Compose_OrderArray'))
// Get the item at index 2 (third item)
outputs('Compose_OrderArray')[2]
// Access a property of the item at index 2
outputs('Compose_OrderArray')[2]?['OrderTotal']
The ? before ['PropertyName'] is the null-conditional operator. It prevents the entire expression from failing if the property doesn't exist on that item — it returns null instead of an error. Use it defensively.
filter(array, expression) returns a new array containing only the items for which the expression evaluates to true. This is your primary tool for querying arrays.
Scenario: You've retrieved a list of all support tickets from a REST API. The array looks like this:
[
{"id": "TKT-001", "status": "open", "priority": "high", "region": "EMEA", "age_days": 5},
{"id": "TKT-002", "status": "closed", "priority": "high", "region": "EMEA", "age_days": 12},
{"id": "TKT-003", "status": "open", "priority": "low", "region": "AMER", "age_days": 2},
{"id": "TKT-004", "status": "open", "priority": "high", "region": "EMEA", "age_days": 8}
]
You want only the open, high-priority EMEA tickets. In filter(), you reference the current item with item():
filter(
outputs('Compose_AllTickets'),
and(
equals(item()?['status'], 'open'),
and(
equals(item()?['priority'], 'high'),
equals(item()?['region'], 'EMEA')
)
)
)
Result:
[
{"id": "TKT-001", "status": "open", "priority": "high", "region": "EMEA", "age_days": 5},
{"id": "TKT-004", "status": "open", "priority": "high", "region": "EMEA", "age_days": 8}
]
Warning:
filter()in Power Automate expressions is not the same as the OData$filterquery parameter you use in SharePoint or Dataverse actions. The expressionfilter()runs in the flow runtime against an in-memory array. For large datasets, always filter at the source using query parameters before loading into flow memory —filter()on a 10,000-row array will work, but it's wasteful and slow.
select(array, expression) transforms every item in an array, returning a new array of the same length where each item is the result of the expression applied to the original item.
Scenario: You only need id and age_days from the filtered tickets, not the full record, because you're sending this to a downstream API that doesn't accept extra fields.
select(
outputs('Compose_FilteredTickets'),
item()?['id']
)
This returns just the IDs: ["TKT-001", "TKT-004"].
For reshaping into new objects, use select() with an object expression:
select(
outputs('Compose_FilteredTickets'),
createObject(
'ticketId', item()?['id'],
'daysOpen', item()?['age_days'],
'urgencyLabel', if(greater(item()?['age_days'], 7), 'ESCALATE', 'MONITOR')
)
)
Result:
[
{"ticketId": "TKT-001", "daysOpen": 5, "urgencyLabel": "MONITOR"},
{"ticketId": "TKT-004", "daysOpen": 8, "urgencyLabel": "ESCALATE"}
]
Notice the inline if() expression that computes the urgency label based on age. This is the compositional power of the expression language: you can embed any expression inside select(), including other function calls.
Tip:
createObject()was added to Power Automate expressions relatively recently. If you're working with older flows or see documentation using JSON string construction withconcat(),createObject()is the cleaner modern approach.
join(array, delimiter) concatenates all elements of an array into a single string with the specified delimiter between elements.
// Convert array of IDs to comma-separated string for an email
join(outputs('Compose_EscalatedIDs'), ', ')
// Result: "TKT-004"
// All IDs
join(select(outputs('Compose_AllTickets'), item()?['id']), ' | ')
// Result: "TKT-001 | TKT-002 | TKT-003 | TKT-004"
This is particularly useful for building email bodies, generating query strings, and creating display-friendly summaries from array data.
union(array1, array2) returns a new array containing all unique elements from both arrays. Despite the name, it also functions as a deduplication tool when called with two identical arrays:
// Deduplicate an array of tags
union(outputs('Compose_Tags'), outputs('Compose_Tags'))
// If input is ['crm', 'sales', 'crm', 'enterprise'], output is ['crm', 'sales', 'enterprise']
intersection(array1, array2) returns only the elements present in both arrays:
// Which tags exist in both the new record and the approved list?
intersection(
outputs('Compose_SubmittedTags'),
outputs('Compose_ApprovedTags')
)
contains() works on arrays too, not just strings. It checks whether an array includes a specific value:
// Is 'enterprise' in the tags array?
contains(outputs('Compose_Tags'), 'enterprise')
// Returns true or false
For checking whether an array of objects contains an item with a specific property value, you'd combine filter() and length():
// Does any ticket have id 'TKT-003'?
greater(
length(filter(outputs('Compose_AllTickets'), equals(item()?['id'], 'TKT-003'))),
0
)
The secret to maintainable, debuggable expression logic is what I call the Compose Chain Pattern. Rather than writing one enormous nested expression, you build your transformation incrementally, storing each intermediate result in a named Compose action.
Here's the pattern for the order ID extraction scenario we mentioned earlier:
triggerOutputs()?['body']?['order_id']split(outputs('Compose_RawOrderID'), '-')outputs('Compose_OrderIDParts')[2]outputs('Compose_OrderIDParts')[3]int(outputs('Compose_OrderIDParts')[1])Each step is named, each output is visible in the run history, and each one can be referenced by name in any subsequent step. When something breaks, you look at the run history, find the first Compose that has the wrong value, and you've immediately isolated the problem.
This pattern costs you a few extra actions, but the debugging and maintenance benefits are substantial. In production flows that other team members might need to troubleshoot, this is not optional — it's professional practice.
Here's a complete exercise that puts everything together. You'll build a flow that receives a webhook payload of customer records, normalizes them, and outputs a clean JSON array ready for database insertion.
Input payload (from an HTTP trigger):
{
"customers": [
{
"raw_name": "CHEN, DAVID WEI",
"email": " DCHEN@ACMECORP.COM ",
"phone": "(415) 555-0192",
"signup_date": "20231108",
"plan": "enterprise",
"tags": ["sales", "enterprise", "sales", "west-coast"]
},
{
"raw_name": "OKONKWO, AMARA",
"email": "AOKONKWO@GLOBALTECH.IO",
"phone": "800-555-0147",
"signup_date": "20240215",
"plan": "professional",
"tags": ["marketing", "professional"]
},
{
"raw_name": "PATEL, SUNITA RANI",
"email": " SPATEL@STARTUPXYZ.COM",
"phone": "(312)555-0273",
"signup_date": "20240301",
"plan": "enterprise",
"tags": ["sales", "enterprise", "midwest"]
}
]
}
Your task: Build a flow that produces a normalized array where:
last_name and first_name are split from raw_name, trimmed, and lowercasedemail is trimmed and lowercasedphone is stripped to digits and prefixed with +1signup_date is converted to ISO format (YYYY-MM-DD)days_since_signup is calculatedtags are deduplicatedStep-by-step build:
Trigger: HTTP with JSON schema matching the input payload above. Enable schema generation by pasting the sample payload.
Compose_AllCustomers: triggerOutputs()?['body']?['customers']
Compose_EnterpriseOnly:
filter(outputs('Compose_AllCustomers'), equals(item()?['plan'], 'enterprise'))
Compose_Normalized:
select(
outputs('Compose_EnterpriseOnly'),
createObject(
'last_name', trim(toLower(first(split(item()?['raw_name'], ',')))),
'first_name', trim(toLower(last(split(item()?['raw_name'], ',')))),
'email', trim(toLower(item()?['email'])),
'phone', concat('+1', replace(replace(replace(replace(item()?['phone'], '(', ''), ')', ''), '-', ''), ' ', '')),
'signup_date', concat(substring(item()?['signup_date'], 0, 4), '-', substring(item()?['signup_date'], 4, 2), '-', substring(item()?['signup_date'], 6, 2)),
'days_since_signup', div(sub(ticks(utcNow()), ticks(concat(substring(item()?['signup_date'], 0, 4), '-', substring(item()?['signup_date'], 4, 2), '-', substring(item()?['signup_date'], 6, 2), 'T00:00:00Z'))), 864000000000),
'tags', union(item()?['tags'], item()?['tags'])
)
)
Response: Return outputs('Compose_Normalized') as the HTTP response body with Content-Type: application/json.
Expected output:
[
{
"last_name": "chen",
"first_name": "david wei",
"email": "dchen@acmecorp.com",
"phone": "+14155550192",
"signup_date": "2023-11-08",
"days_since_signup": 127,
"tags": ["sales", "enterprise", "west-coast"]
},
{
"last_name": "patel",
"first_name": "sunita rani",
"email": "spatel@startupxyz.com",
"phone": "+13125550273",
"signup_date": "2024-03-01",
"days_since_signup": 14,
"tags": ["sales", "enterprise", "midwest"]
}
]
Notice that Okonkwo is excluded (professional plan), Chen's duplicate "sales" tag is removed, and all values are clean.
This is Power Automate's most cryptic error. It means your expression has a syntax error. Common causes:
'. If you copy-paste from Word or some web pages, you may get curly quotes ' which break everything.substring(text 0 4) instead of substring(text, 0, 4).This usually means you're trying to access a property on a null value. Add null checks:
// Risky:
item()?['Address']?['City']
// Safer in a condition:
if(empty(item()?['Address']), 'Unknown', item()?['Address']?['City'])
The most common cause is a type mismatch. If your array contains "age": "5" (string) and you write greater(item()?['age'], 7), the comparison fails silently because you're comparing a string to an integer. Convert explicitly:
greater(int(item()?['age']), 7)
Similarly, equals(item()?['status'], 'open') is case-sensitive. OPEN does not equal open. Add toLower() defensively:
equals(toLower(item()?['status']), 'open')
Covered earlier, but worth repeating: ticks('2024-03-15') will often fail. Use ticks('2024-03-15T00:00:00Z') instead.
If you use select() inside an Apply to Each loop, the item() function inside select() refers to the select() array item, not the Apply to Each loop item. You lose access to the outer loop context. The solution is to compute the array using select() in a Compose action before the loop, or use variables to capture outer loop values before passing them into nested expressions.
APIs frequently return numeric values as strings in JSON. "OrderTotal": "1250.00" looks like a number but is a string. Arithmetic operations will fail or return unexpected results. Use float(item()?['OrderTotal']) or int(item()?['OrderTotal']) to convert before doing math.
Expression-based transformation runs in the flow runtime and is billed as part of your action executions. For small to medium arrays (say, under a few hundred items), filter() and select() in expressions are perfectly appropriate and significantly more efficient than using Apply to Each loops with conditional steps.
However, for large arrays — thousands of items — the memory and execution overhead can cause timeouts or throttling. In those cases:
$filter for SharePoint/Dataverse, SQL WHERE clauses, or API query parameters to reduce payload size before it enters your flow.The expression language is powerful, but it's not a data processing engine. Use it for lightweight shaping and enrichment; delegate heavy lifting to appropriate infrastructure.
You've moved well past "drag-and-drop" automation. Here's what you now have in your toolkit:
String functions: concat, substring, indexOf, replace, toLower, toUpper, trim, split, startsWith, endsWith, contains — enough to clean and reformat virtually any text data you'll encounter.
Date functions: utcNow, formatDateTime, addDays, addHours, ticks, dayOfWeek — enough to handle scheduling logic, duration calculations, and cross-format date normalization.
Array functions: filter, select, first, last, join, union, intersection, length, contains — enough to query, reshape, and aggregate collections without writing a single loop.
The Compose Chain Pattern: Break complex transformations into named intermediate steps for debuggability and team readability.
Where to go next:
xpath() and json() functions for working with XML payloads and parsing raw JSON strings — essential if you're integrating with SOAP APIs or legacy systems.try/catch patterns using scope actions and the result() function — production flows need resilience, not just happy-path success.The expression language has rough edges, but once you internalize the function signatures and the Compose chain discipline, you'll find that most data transformation problems that once seemed to require custom code can be solved entirely within the flow editor. That's a meaningful capability to have.