Welcome once again to another Power Automate Best practices, Tips, and Tricks. In my previous blog posts, I talked about some of the most essential best practices you should have while working with the Power Automate:
And some tips and tricks:
- #4 Copy to my clipboard
- #5 Using Scopes
- #6 Error handling… configure run after settings
- #7 Delete notes
- #8 Learn from failures
Today I’m going to speak about in my opinion one of the most annoying features/tasks and recommend some Best practices, Tips, and Tricks to minimize it: working with expressions.
#9 Expressions nightmare
Power Automate is a powerful tool for automation, with tones of connectors and the things you need to do work to make the design of a flow easy with a rich design experience – except when it comes to expressions. To work with expressions you will need skills, a good dose of patience, and you’re expected to know some relatively arcane formula expression rules to make your steps work together.
Power Automate Expressions can be used to manipulate incoming messages. Such manipulations can be used for strings, dates, and times, XML, or JSON. Functions allow you to do all kinds of expressions such as:
- String functions
- Collection functions
- Logical comparison functions
- Conversion functions
- Math functions
- Date and time functions
- Workflow functions
- URI parsing functions
- Manipulation functions: JSON & XML
You perform these functions on an item or property of a JSON document or perform another function on the result of a previous function (nesting).
But first things first! Let’s see the most common function the body function and what is that strange string on it?
The body function returns the message body of a previous action at runtime. And that strange string value inside it is in fact the action name with a specific particularity: the spaces of the action or trigger name are replaced by underscores. In this particular case, we are accessing or getting the message output body to the action “Execute SP – Get BZT lock error details“.
The problem with Expressions is the fact that the Power Automate flow Editor has several issues:
- The Expression on the action will be represented by a small pink rectangle with the first function name and 3 dots inside parentheses
- The “pop-up” Expression window is not resizable – and to think that after all these years, Microsoft would not make the same mistakes over and over again – neither is the textbox resizable! Which makes it practically impossible to read or elaborate on more complex expressions in a simple and correct way
- If that wasn’t enough, to help the party, this annoying Expression pop-up window typically appears at the end of the action we are working on, at the beginning of the action we are working on… everywhere except where we want it to be! It doesn’t follow you.
Which makes it impossible for us to be productive! This is a task that only wastes development time.
And some of these Expressions can become quite larges, for example:
- body(‘Execute_SP_-_Get_BZT_lock_error_details‘)?[‘ResultSets‘]?[‘Table1’][0]?[‘ErrorDescription’]
- Get the first ErrorDescription value inside the ResultSets – Table1 from the action Execute SP – Get BZT lock error details.
- ResultSets – Table1 is the result of a stored procedure call.
- concat(div(sub(ticks(utcNow()), ticks(item()?[‘Created‘])), 10000000), ‘ segundo(s)’)
- Honestly I don’t remember this expression, I have developed it for a long time but I believe it is the difference in seconds between two dates
- int(div(add(add(int(variables(‘ContentClear‘)), int(variables(‘GoodCommunicator‘))) , add(int(variables(‘EnjoySession‘)), int(variables(‘MetExpectation‘)))),4))
- The calculation of the average of 4 numbers (only the int value)
And these are just some basic examples!
This is a more complex one:
- if(greater(length(body(‘Parse_JSON_-_Global_Tokenizing_Read_Entities_response_message_content’)?[‘customer’]),0),body(‘Parse_JSON_-_Global_Tokenizing_Read_Entities_response_message_content’)?[‘customer’][0]?[‘accountDescription’],”)
- Get the first accountDescription inside the array customer if it exists, otherwise set the value and empty string: ”
How can you be more productive with Expressions?
For more complex expressions, you should use the Power Automate Expression editor to check the available Functions and expect parameters, but for actually creating the expression itself, do not do it inside the Power Automate Expression editor. Instead, use the… notepad++ or another simple text editor.
Taking as an example, the calculation of the average of 4 numbers (only the int value), which is a somewhat trivial operation using the existing Functions inside Power Automate, is not as simple as you may think. First of all, the Add function only accepts two parameters which means that we will have to have sum nested operations, then divide by four and finally convert to int. So the easy way is to ident in notepad++ this formula in an indented and easy-to-read manner.
Once we know that our formula is well created, convert it into one line:
- int(div(add(add(int(variables(‘ContentClear‘)), int(variables(‘GoodCommunicator‘))) , add(int(variables(‘EnjoySession‘)), int(variables(‘MetExpectation‘)))),4))
And copy it to the Power Automate Expression editor.
Old school indeed, but you are warned… for creating expressions, use notepadd++!
Common Expressions to get values
There are several ways to get the values from other parts of your Power Automate business logic, depending on what you want to access and from where. These are some of the most important ones:
- From variables: to fetch one of the variables you’ve defined in your Power Automate, you need to use the variable function and include the variable you want to use in single quotes like this:
- variables(‘varMyVariable’)
- From the trigger: to fetch the body or tokens properties from the trigger, we need to use the triggerBody function. If I were fetching the ID of an item that triggered (assuming that’s it as an ID), you would write like this:
- triggerBody()?[‘ID’]
- The ? character is used if you are not sure if it exists. In this case, this would return the item’s ID or null.
- From other outputs: to fetch a different item or some other outputs from previous actions, we will use the body function and include the action name (replace spaces with an underscore as we mentioned earlier) from where you want to fetch.
- body(‘Parse_JSON_-_Global_Tokenizing_Read_Entities_response_message_content’)?[‘customer’][0]?[‘accountDescription’]
- Inside For Each step: One other common condition is when you’re working on a set of items in a For Each step. If you’re in a For Each step, you can get the values from the current item with item(). If you wanted to get the ID of the current item, you would write like this:
- item()?[‘ID’]
Stay tuned for the following Power Automate Best practices, Tips, and Tricks.
