Concatenating strings that could be empty

The Trim+Concat Hack 

Why is this hack needed? Let's say you are trying to compile a list of names using records from the Contacts dataverse table. But maybe some of the contact have an academic title. And if they do, you'd like to prepend that title to their names. 

The straight-forward way would be to use an if() expression, similar to this:

if(

    equals(

        empty(

            items('Apply_to_each')?['academic_title']

        ),

        true

    ),

    items('Apply_to_each')?['fullname'],

    concat(

        items('Apply_to_each')?['academic_title'],

        ' ',

        items('Apply_to_each')?['fullname']

    )

)

... a long and messy expression, especially due to the branching. This may also be hard to debug...

Instead use trim(). Wait... trim?? Yep, you heard right. The following simplified expression is only about 50% the size and much easier to read.

trim(

    concat(

        items('Apply_to_each')?['academic_title'],

        ' ',

        items('Apply_to_each')?['fullname']

    )

)

You probably figured it out. The trim()'s job is to eliminate the whitespace introduced by concat(), should the academic title be empty. 

For longer concatenation chains, you may have to approach it in chunks of two, since trim can only eliminate a whitespace if it's at the beginning or end of a string.

Coalesce

If you haven't done so, also check out coalesce()! Like our hack above, coalesce() can make your expression easier to read and understand. You would use it, when your input string may be empty (again), but if it's empty, coalesce() let's you provide a replacement value. 

coalesce(inputValue, replacement_value_if_inputArgument_is_empty)

You can even chain multiple arguments together and coalesce will pick the first argument that is not empty. E.g. with a hard-coded string at the end to signal that all of the arguments were empty.

coalesce(firstArgument, secondArgument, thirdArgument,'NO_VALUE')

Get Sharepoint List items from a list of IDs provided by the user as trigger input

You would like to let the user provide a list of IDs as an input trigger and the action "Get items" should then load only the items in that list? Here is a quick solution for this problem. As a bonus, the flow will simply load all items, if no input was provided by the user.

The approach uses a custom filter query extension to load only these specified input IDs. 

Customize the flow's trigger input. Important: Make the field is optional by clicking on the three dots next to the input and selecting "Make field optional". Here is an example.

Then initialize a string that will help us build the filter query extension.

Then add a condition that checks, if the user has provided some trigger input (e.g. "1, 2, 3") when running the flow. Use the expression  triggerBody()?['text'] on the left. The question mark is crucial here! It assures the flow won't crash when there is no trigger input, but rather return  null.

Inside the "true" branch, let's compile the query extension now. The inserted expression is:

join(split(replace(triggerBody()['text'],' ',''),','),' or ID eq ')

This expression does three things at once:

a) remove all whitespaces

b) split the string (using the coma as delimiter) into a temporary array

c) join the array, using the phrase ' or ID eq ' in-between the ID values

The result may look something likes this (for the given trigger input: "1, 2, 3"):

 and (EquipmentID eq 1 or EquipmentID eq 2 or EquipmentID eq 3 )

All that's left to do, is to append this string variable to the filter query of your "Get items" as dynamic content. Here is an example:

Note: If the user didn't provide an trigger input parameters, then the variable will remain empty and the filter query remains unchanged, otherwise our concocted query extension will make sure only the IDs are loaded specified by the user.

"ID" is of course only an example, you can also use this approach to custom filter other Sharepoint list columns. 

Power Automate - A Collection of Best Practices

 This is a personal collection of best practices for Power Automate. This individual practices are only mentioned, not explained. However, feel free to comment and ask me to elaborate on a specific point! This list ist likely to grow in the future...

Code / Flow Quality and Efficiency

• Always give your actions good names that describe that they do without getting to long. If you need to provide more info, do it with the "Add note" feature. 

• Good names for conditions: Frame them as a question (without the question mark, since it's not allowed). Some examples: "does array have a single result", "any links to add", "is tomorrow the first day of the month". 

• Good names for actions: Keep their default name, but add some details thereafter. E.g. "Filter Array - matching email", "Create an email - notification to operator", ...

• Use scopes. Two reasons! 1) They help to make your flow more lean by collapsing actions that solve a specific problem within the flow into a single object. 2) If anything fails inside a scope, then the flow doesn't immediately crash. Instead the surrounding scope will fail. You can then use a "configure run after" to handle the failing scope. This technique is part of the "try - catch - finally" approach which makes your flows MUCH more robust!

• Limit your use of "Apply to each". It is fairly slow! Use Actions like "Filter Array", "Select", or join() and union() functions to accomplish your goals. The difference in speed is staggering! 

• Variables vs compose statement: Know when to use which! 
• Variables: 
• have to be initialized (has to be outside of a loop).
• data type has to be declared when initializing it.
• can be set as many times as you like 
• loops with access to variables inside should have concurreny = 1
• can't access themselves (self-referential); but specific actions exist to increment and append to their values
• Compose:
• Can only be set once - inside the Compose action
• very flexible, can even be created inside of loops
• will assume the data type of its input (e.g. from dynamic content or expression)
• Very handy when debugging your Code, especially before Conditions, since conditions don't show their values (right or left side) during runtime.

Robustness and Security

• Use service accounts for business-critical flows. This may also reduce licensing costs.

• "Apply to each" loops with variables inside should always have concurrency = 1. Otherwise parallel executions of the loops will lead to unpredictable outcomes

• If you have a whole tree of child flows, carefully draft an error handling procedure: What errors are handled inside the child flow, which ones are passed upwards and which ones are simply ignored. Keep a list of this.

• Instead of hard-coding things that you expect to change in your flows, consider building a Configurations table with key-value pairs: e.g. operator_email, website_link, version_number... Then build a child flow that returns the value of a configuration given a specific configuration key. Include the Configuration table in your documentation to the operating users.

• Never use actions marked with "(Preview)" for anything remotely business-critical! Previewed actions are very likely to become obsolete, as soon as the non-preview version is released. This will break your flows! It's of course fine to use them for experimenting with upcoming features though.

Data Sources

• Avoid excel lists as data sources

• If you use a Sharepoint list as data source, do it only once per flow! I see often that people use "get items" inside an "Apply to each". Instead use Filter Array to load the row(s) from the whole datasource. Filter Arrays are extremely useful and fast.

Dataverse

• Refer to a Choice column always by its value, not its label. Labels may change over time, but values tend to stay the same, since they are usually not exposed to the user. This is also faster btw. But as a negative consequence though, your Power Automate code will be less easy to read...

• Your Dataverse tables always should have two IDs, an internal ID (containing the GUID of the record, already present in table, column name == table name) and a human-readable ID (can be configured as auto-generate, e.g. "rec-000024"). Let the users refer to the record by their human-readable ID. But then inside your flow first search for the that record and then specifically load it with "Get Row by ID" (providing it the resulting internal ID of your search). This has the advantage that you won't get back an array of rows, but only a single record. A good way to avoid the "Apply to each" chaos when dealing with arrays of rows...

Copilot

• (As of June 2024) Forget about Copilot inside of Power Automate! Use ChatGPT 4o instead. It is about 1000x more useful for this. But be aware that ChatGPT may gaslight you with inexistent functions and actions :D.

Persistent Array: Storing and Retrieving an Array using JSON

Sometimes it makes sense to store and retrieve array data directly, without writing it to a database (or Sharepoint list). This is sometimes called "Block Storage". This option is particularly useful, if you are limited to Sharepoint lists due to a lack of premium licensing. Sharepoint lists are quite slow to write or read, especially if they are large...

When is this a good option?

This can be a good option, if any of the following situations apply:

• your array is large (e.g. larger than 1000 indices)
• access speed to your persistent array data is crucial (Sharepoint list too slow!)

How to build the flows

Storage

Convert the array to a string, then to a json format, then store it to OneDrive.

Retrieval

Get the file content, then use the expression 

json(base64ToString(body('Get_file_content')?['$content']))

to convert it back to a format that can be used to intialize your array.

Best Practices: Sending Warnings and Error Notifications

 This is the first post of a series I intend to make about general best practices in Power Automate. My goal is to suggest more general approaches and mind sets when designing for a bigger projects. So these may not be a one-size-fits-all solution, but always a good starting point from which to expand.

What's the issue with warnings and error notifications?

Let's come straight to the point. The built-in system for error reporting is quite inadequate, at least for professional uses. Simply sending an email to the flow owner "Your flow has failed" is not an error notification! 

From the flow owner's perspective, the minimum I would expect is that the notification shows what exactly has failed. But what's worse, the owner of the flow may not be always around. In fact, it's quite common that flow development is done by an external contractor and who, after a period of roll-out, will no longer be available each time a flow fails. What then? 

Notification abstraction

And what about warnings? The reason I included them with the error notifications is that I'd like to propose a continuous issue reporting system that distinguishes severity levels and strives for a report that is complete, precise and helpful. For this reason, I'd like to propose the following system:

• create a child flow "SendIssueNotification" that has as input parameter: 
• Notification title
• Severity level
• Issue class (flow execution, API, user input, etc.)
• Notification content
• store the recipient of the notification in a configuration table (key/value)

The goal of this system is to minimize the actions inside the flows that deals with creating notifications. Let's offload all of that to the "SendIssueNotification" child flow, which can also apply some cosmetic features to the notification - e.g. making major errors more prominent. 

Notification Recipient(s)

As mentioned above, the recipient of the notification is stored in a separate database. This could be a general configurations table for your project, or, if different notifications should go out to different people, e.g. a role-based approach, this could be part of a separate Roles table. 

Also ask yourself, how should your issue reporting system handle...

• recipients leaving the organisation (can be checked using the profile actions)
• recipients being out-of-office (e.g. on vacation; can be checked in Office 365!)
• should each role have a substitute?
• who will be tasked with keeping this table up to date?

The Problem Code table

Remember, our goal is to reduce error handling actions inside our flows to the necessary minimum. One logical idea then would be to offload it completely to a separate database table. This table consists of all the necessary information to generate a notification: Title, severity level, issue class and content. But in addition to that, it also holds a suggestion for resolution, as well as a unique problem code name, by which the issue will be referred to. We can use such a system alongside the child flow "SendIssueNotification" - in fact it's simply a new child flow "RaiseIssueByCode" that accepts only that problem code, loads the row in the problem code table and then calls "SendIssueNotification". We cannot get rid of "SendIssueNotification" altogether because in your flows, you may encounter situations when you would like to pass an html table with data along side your notification content. However, if this is a very common occurency, you may even contemplate adding a third child flow that extends RaiseIssueByCode with an html table as input. 

So, basically, we've built abstractions to all of our issue notifications. Which makes applying changes to it very easy. 

What's the price we pay for this flexibility? Not much! Sure, we've increased the number of flows being executed, because we haven't created the emails inside our flows, but delegated the task to child flows. But we've also made our flows leaner and easier to maintain. 

Hardening Child Flows, Part 1: Assertions

 This is part one of a new blog series about hardening child flows. The term hardening originates from metal work, where oil is used to quench hot steel in order to make it much more robust. This is also our goal. Our child flows should be as reliable as possible. More specifically:

• They should not fail unexpectedly
• They should handle unexpected input data as well as expected input data an react accordingly
• They should not fail if a contained action takes longer than expected (timeout & retry policy)
• They should have a continuance plan for each failed action (e.g. continue anyway, handle, send error message to operator and then terminate, e.g.)
• Prior to making any sort of writing operation in the database, the input data needs to be checked (assertions)

Today, we will have a look at the last bullet point, assertions, since it's the easiest to grasp from the hardening goals mentioned. 

What are assertions

Think of assertions as conditions in the form of a boolean statement. For example, "is the input value greater or equal to zero?". If the condition is met (true), the flow is allowed to continue. If it isn't met (false), then typically the flow is terminated, but there are other options of course, that would allow your flow to execute anyway, e.g. by use of a default value instead. Assertions should always be the first actions in your flow, giving you the peace of mind, if a run made it through the assertions, the input data is OK which makes writing the rest of your actions in the flow much more easy, because you don't have to crowd your flow with special handling conditions for every possible situation regarding your input values. 

Some programming languages, like Python, have a command called assertion, which is delightfully short and concise:

assert i >=0, "Input value below zero!"

Power Automate, unfortunately, doesn't have such an action built-in. But we can easily recreate it with our own actions. Let's look at some examples

Method 0 (don't use): Terminate on failed assertion

While this is the most straight-forward method, but it has some obvious limitation. Simply letting a child flow fail is not good practice. We can do better!

At least, we managed to avoid having the flow continue with a value outside of the expected range, therefor avoiding further complications inside the subsequent actions...

Btw, don't bother writing a message in the Terminate action. Nobody will see it...

Method 1: Send a message and then terminate

Now at least a pre-defined person will receive a warning before the flow fails. This is the best option, if there is really no better way to handle the failed assertion: The usage of a default value would not be appropriate or the usage of the value cannot be skipped in subsequent actions. 

Just some quick tips: Never hard-code an email address of the person who should receive these messages. People leave companies, it happens :). A smarter way would be to put the person's email in a separate table, either specific to the project or, even better, used by all projects for role-based automated communication. Then create a child flow "Send notification to operator" where all the necessary steps of getting the person's email, compiling the email and sending it are done, allowing you to concentrate in your current flow on the message content. 

Method 2: Limit the value range with compose - don't terminate

If the fact that your input value is below zero can simply be ignored in your flow and a default value (e.g. 0 in this example) can be used instead, then you could use a compose action to set the lower limit of your value. Just make sure to use the output of this compose instead of the trigger input value throughout your flow. 

Of course the major advantage is, that the flow is allowed to continue. This may however mask a potential problem with your data. Beware!

Method 3: Use coalesce() to catch empty input value (null)

The coalesce() Function is a great way to assure a value is not null. Basically it evaluates: 

Is a given value null? 

No? Great, use it as is. 

Yes, it's null? Then use this instead ... 

Final tip: Put your assertions in a scope

Putting all of your assertions in a scope we keep them separated from the rest of your flow, hence improving legibility of your flow.

Avoiding nested loops and conditions in Power Automate

What's the issue with nesting? 

Nested loops or conditions make your flow progressively more difficult to read and understand. And quite often they are unnecessary. In the case of loops (Apply to each), Power Automate is quite trigger happy and immediately encloses new actions in an Apply to each loop when you access an element of an array. And in the case of conditions, you often only need actions in one branch - but the second (empty) branch still takes up screen real estate. Also important, Power Automate has a nesting limit of 8 levels, not that I expect many of us to run into this limitation. Last but  not least, Apply to each loops are painfully slow, especially when variables are written inside the loop. But that's a topic for another post. 

What are your options?

For nested "Apply to each" loops: 

• Replacing actions inside your Apply to each loop with Filter and/or Select actions. 
• Only need to access the first element in the array? The function first() is a good option (although with some pitfalls...)
•  Doing complex things inside your loop? Have you considered encapsulating it in its own child flow?

For nested conditions:

• Consider using an if() expression instead of a condition
• Use the Terminate action to remove some of the nesting levels, especially while checking for halting conditions ("Does X contain a value? If not -> exit flow")
• Have you considered using a Switch action instead of a nested condition?
• Have you tried adding multiple condition rows to your condition?

Let's quickly look at some of these options.

Filter and Select actions

These are extremely useful and efficient actions to handle arrays. In fact, they are so efficient that they often can be completed in a 1/100th of the time it takes to accomplish the same thing with an Apply to each loop. I encourage you to really try and master these two actions! A good place to learn them is on DamoBird365's Youtube videos, e.g. this one. A great introduction to Select by Alireza Aliabadi you can finde here.

first()

Let's say your variable called names contains three names: ["Joe", "Bill", "Max"].

first(variables('names') will of course return Joe. So couldn't you just use first() in all cases where an Apply to each loop is unnecessarily created? You could, yes, just be aware that first(), when used on an empty array, will return null, which maybe be incompatible with subsequent actions in your flow. 

Loading a single Dataverse row without first()

You may have noticed that eventhough your primarily name column may only contain unique entries, trying to load a single entry using such a name column will always return an array (with one or zero rows in it). That means every time you'd like to access data in that row you'd have to use first()? There are better solutions!
a) Load the row based on the unique GUID, using the action "Get a Row by ID". This is guaranteed to return only a single row. If necessary load this GUID using a separate "List Rows" (Dataverse). In the subsequent actions, always refer to the output of "Get a Row by ID" - no first() required!

Loading a single Sharepoint list row without first()?

The approach is similar to the method for Dataverse. "Get item" will try an load a Sharepoint list row using the column "ID". It's also a hidden column, that's created automatically. So you may have to first use "Get items" with a Filter query to extract the hidden ID value. Check out the example below. Now you can use dynamic content to access the values inside of your target row!

Conclusion

Let me know, if there's a specific approach that you'd like to hear more about.