7 Power Automate Flow Tips and 7 Reasons Why Flow is Annoying

7 Power Automate Flow Tips

Here are some notes from a recent project - drink it in.

Reliability & Debugging

Tip 1 – Save Often to Surface Hidden Errors

An “Apply to each” block can have an error nested deep making it hard to determine where the error is, giving just “The input parameter(s) of operation ‘Apply_to_each’ contains invalid expression(s). Fix invalid expression(s) for the input parameter(s) of operation ‘Apply_to_each’.”

Tip 2 – Recreate Flaky Actions

Sometimes “this contains invalid expression(s)” error is just Power Automate being whimsical and finicky. Deleting and recreating the offending action may resolve it. This is one reason I feel that Power Automate is a substandard development platform—more brittle than the old GW-BASIC days, and that’s saying something.

SharePoint Field Access

Tip 3 – Reference SharePoint Columns Properly

To reference a column within SharePoint “Get files (properties only)”, for example for a column named “PurgeDate”, use item()?['PurgeDate'].

Tip 4 – Reference Computed Objects with Braces

To reference an item’s path, you need braces, e.g., item()?['{FullPath}']. The braces are needed for returned computed objects which are not actual metadata.

Tip 5 – Count Path Depth with length() and split()

To determine how many path segments (folders) there are to your item, count the slashes with the length() function:

length(split(item()?['{FullPath}'],'/'))

A value of 2 is top-level; 3 is a subfolder, etc.

Filtering & Performance

Tip 6 – Use substringof() Instead of contains() in OData Filters

The OData contains function does not work in Power Automate, but substringof does. So instead of contains(FileLeafRef, 'AndyTest'), use:

substringof('AndyTest', FileLeafRef)

Tip 7 – Use Lowercase ‘or’ in OData Queries

Despite using the uppercase OR in its tooltip, this is invalid syntax. Use lowercase. This query returns only folders containing “AndyTest” within its name, reducing my runtime from 15 minutes to under a second and running only on my test folders, not production folders:

7 Ways Power Automate Flow Could Better

I’ll admit up front that my experiences with “no code” and “low code” are mostly frustrating especially if you need to do anything interesting (read “complicated”). That said, often Power Automate Flows require the least effort for the entire life cycle of a project relative to other development platforms such as SharePoint Framework, Azure Functions, etc.

1. Quit it with the invalid expression when it is valid. There should be a way for it to clear itself and sort it out.
2. Show me the actual action that is not letting me save my Flow, not rolled up to its great-great-great-grandparent.
3. It would be nice to be able to edit the JSON directly in Code View. If I have a syntax error, fine, don’t let me save. But being able to copy paste from other actions and Flows would save me so much time. Note that if you really, really need to edit the JSON, you can export your flow, unzip, edit the JSON, rezip and reimport. The details are beyond the scope of this post.
4. I don’t think there is a good way to indicate where Copilot should add an action. If you have multiple places where you can add actions due to dead-end if statements, it just picks one.
5. Renaming actions enables a bit of documentation for easier maintenance, but there is no obvious way to determine what the action is after you have renamed it. This should be in the “About” tab of the action. For example, I renamed a SharePoint Get files (properties only) Action to “GetAllFolders”, but there is no way to determine the original name of the action as the operationId in Code View is different.
6. The new Flow designer has its merits but is still missing functionality. I’m numb to Microsoft removing features as they move to new, “modern” feature sets which has less and less of some of the richer features that improve productivity. This has been going on for decades, something I first noticed in the Office suite in the 1990’s when they dumbed down the interface, I suppose to make the initial learning curve less steep.
7. Gracefully handling errors during runtime seems more complicated than it needs to be and feels kludgy, but that is similar to how a lot of Power Automate is.

This was cathartic. Hope you enjoyed the 7 & 7. Cheers!