Testing quickly if Dataverse fields are empty

 Here is a quick hack, to test, if specific Dataverse fields of a record are empty (or null). 

The normal way would be to insert a "is equal to null" condition into your flow for each field you'd like to check. That's too much work!

The following method is safer, quicker to write and performs much faster on top of that!

You will need four actions: 

• a "Get Row by ID" (Dataverse) action that loads the Dataverse record to be tested

• a compose that holds an array of field names. You can add as many fields as you like here, but they should be the logical field names.

• a filter array to iterate over this array and perform the "is-null" checks. Note: Normally filter array is performed on an array of data elements, for example an array of Dataverse records. This is NOT the case here. We stay in one record and iterate over some of its fields instead.

• a condition that counts the number of "is-null" fields found by the filter array.

Array of field names (Compose)

Here is an example of what this array could look like:

["abc_fullname",

  "abc_birthday",

  "abc_department,

  "abc_supervisor"]

Filter array

From:     outputs('Compose')

Map: 

empty(string(coalesce(outputs('Get_Row_by_ID')?['body']?[item()],'')))

"is equal to"

true

Let me quickly explain the mapping function. Remember that item() acts as an iterator function, applied to all the array elements loaded into the "From:" field. 

The first element is "abc_fullname", which is a field name and it is used as such to load the value of a record field with that field name:

outputs('Get_Row_by_ID')?['body']?[item()]

becomes

outputs('Get_Row_by_ID')?['body']?['abc_fullname']

The stored value of this field could be a variety of things, a string, a number, a lookup, null. Since all we are about here, is, whether it is empty, we can use the function empty(). 

However, empty expects a string as input, so we need to cast the field value as a string, using string(). 

string() will produce an error though, if the field value is null, so we also use coalesce(), one of my favorite functions in Power Automate, to make sure the value is at least an empty string.

Condition

length(body('Filter_array'))

"is equal to"

0

Insert your business logic in the "If yes" branch (no fields are empty or null) and in the "If no" branch (some fields are empty or null). 

Asynchronous Flow Design - Effient, Lean, Robust and Testable!

Power Automate beginners are often tempted to pack a whole process into a single flow. The advantages seem obvious: less clutter, easy to keep an overview over all runs, etc. However, flows quickly become very complicated and fragile. 

I'd like to propose an approach that is inspired from asynchronous software design: Chop up a process into stages and handle each stage with an asynchronous child flow. 

Each flow, if completed successfully, will then "hand-over" to the next child flow. This approach works very well for automation processes on a Dataverse records (however, the same approach could also be used for SharePoint lists).

Pre-Requesites

• A column carrying a "WorkflowStatus" with a Choice value for each of the stages of the automation process,
  examples: "new", "stored", "checked", "confirmed", "rejected", "approved", "completed"
  
  
• A child flow for each of the stages.
  examples: "NewEntry", "StoreEntry", "CheckEntry", "ConfirmationApproval", "RejectEntry", "ApproveEntry", "FinalizeEntry"

Asynchronous Child Flow design (Best Practice)

Each Child Flow architect follows the following pattern: 

1. Trigger Action = "Manually trigger a flow".
   Argument: GUID (of the Dataverse record)
   
   
2. Action "Respond to Power Apps or flow". No return argument.
   This will release the calling child flow right away ("Hey, I got it now, you can go have a rest.")
   
   
3. "Get a row by ID (Dataverse)" (using the GUID)
   
   
4. Assert that the WorkflowStatus is allowed to be processed by the current child flow (=the current stage of the process).
   If not, terminate with message to support.
   
   
5. Further assertions on values stored in the record.
   Assertions, generally speaking, are expected states, which you don't actually expect to fail. But it's an excellent defensive programming practice to assure that they do meet your expectations. Not only will it make testing easier, assertions will act as built-in sanity checks for future updates to the flow.
   If any of these assertions fail, terminate with a message to support.
   
   
6. Only now, set the WorkflowStatus to the name of the current stage. Why so late? Only now, the flow is ready to perform it's core duties - everything before was just prep.
   
   
7. Perform all operative steps of the current process stage.
   In case of errors, terminate with message to support.
   Some of the actions may build upon each other. In this case, errors should cause an early termination with message to support.
   
   
8. Launch next child flow in the chain. Even out-of-sequence calls are possible here (#4 would safeguard against illegal out-of-sequence calls).

Robust Error Handling

Create a separate child flow "Support Case".

Trigger Input #1: GUID (just like above)

Trigger Input #2: "pre or post". The calling child flow can specify whether it failed pre- or post-operation. This will define the point of reentry to the process chain (after the support case is resolved).

Inside "Support Case" create an approval to the Support Worker, which gives them the possibility to manually adjust the record, check run histories, etc. and then click on "Approve" to set the record as resolved. Based on the the "pre- or post-operation" trigger option, it will re-trigger the calling flow ("pre") or trigger then next flow in the process chain ("post").

Error collection is done in each of the child flows belonging to the process chain. An easy way of doing this is to initialize a string array (e.g. "errors") and then append a message to it whenever something goes wrong. Between each of the major steps above in "Child Flow Design", test if the length of the errors array > 0, if yes, launch a "Support Case". 

In order to pass the messages from the errors array, you can either pass them via a child flow trigger argument, or, even better, write it to a special "Errors" column of the Dataverse record. The second option is more persistent, and in general it's a good idea to keep the interface lean between flows (= keep the child flow trigger arguments to an absolute minimum), usually you just need the record GUID. 

Advantages of Asynchronous Flow Design

• Flexibility: out-of-sequence calling is possible without becoming a programming nightmare
  
  
• Manual reactivation after failture: Should your flow fail and you manage to find the reason for it: Fix the flow (and/or dataverse record), then simply run that flow manually with the GUID that caused the failure. It will then continue its asynchronous journey, as if nothing had happened. 
  
  
• Efficiency: Each flow only completes a part of the process and then hands-over to the next flow
  
  
• Timeout robustness: Remember, each flow can only be running for 30 days, so if you try to chain multiple approvals together, it's very likely, you will sometimes hit this limit. However, if only one approval is allowed per flow, you can very easily extend timeout limits to much much longer process durations. In theory: 30 days x the number of the flows in the process chain.
  
  
• Testability: It's very nice to be able to test each stage individually without having to retrigger the whole chain of flows!
  
  

I hope this approach is something that will benefit you as well in your next project! Good luck.

Make flows go VROOOOOM

When designing for fast flows, there are a few optimizations you need to consider. Let's get right into it.

Avoid slow data sources

Try to avoid slow data sources, like excel tables or sharepoint lists. Excel lists are really not a good option for Power Automate, I'm guessing you already found that out the hard way. Sharepoint lists are not quite as bad (or slow), but still - if you have a premium license, you should as much as possible opt for Dataverse tables (or alternative modern database architectures). The difference in speed is STAGGERING. But even when you use Dataverse tables, there are some tips to speed things up even more. But let's stick with the most important acceleration techniques first.

Avoid 'Apply to each'

If you can, try to avoid Apply to each loops! Much of what is being accomplished in Apply to each loops can also be done with:

• Filter Array
• Select
• array functions, like join(), union() or split()

Try to load table content only once per flow

In an ideal flow, you load your data at the top. If you need a subsection of the records, use filtering (Filter Array). And in order to do that use filter queries or in the case of dataverse, even better...

Familiarize yourself with FetchXML (Dataverse)

I had no idea how insanely powerful FetchXML is for fetching Dataverse table records. I usually create them with the plugin "FetchXML Tester" in the XrmToolBox and then use them in my flows. These are especially useful if you need to load data from multiple tables at once with complicated logical search dependencies. Most of my flows that use FetchXML now run in under a second, because all the data is fetched only once per flow. Think of FetchXML as filter queries on steroids.

Go parallel, if possible (especially in Apply to each)

If you need to use Apply to each (e.g. if you need to update records one by one), then design them so they can run in parallel. This means, try to avoid setting variables inside of loops and use compose instead. This isn't always possible, sadly. And sometimes variables ARE the only viable solution. 
Also if branches of your code can run in parallel, consider doing it. However when joining the branches be careful, each branch may continue the flow execution on its own which will produce some weird output! The way to fix this is to use "configure run after" in the joining action and configure it to only proceed when all branches have completed successfully. 

Send your conditions on a diet

Conditions are a surprising slow down for flows, especially if your conditions have many rows (=logical statements). Your first option should be to avoid conditions altogether with filter arrays, but if you have no choice try to condence them into one logical statement. 

As an example, let's say you want to create a condition that checks if 5 variables. Only if none of them are null, should the yes branch be called. Usually, your condition would look like this:

variables('my_variable_1')    -> equals -> null
variables('my_variable_2')    -> equals -> null
variables('my_variable_3')    -> equals -> null

variables('my_variable_4')    -> equals -> null

variables('my_variable_5')    -> equals -> null

Slooooowwww...

You can do the same by chaining together 5 coalesce() functions and accomplish the same thing with one logical statement:

coalesce(variables('my_variable_1') , coalesce(variables('my_variable_2') , coalesce(variables('my_variable_3') , coalesce(variables('my_variable_4') , coalesce(variables('my_variable_5') , null))))))   -> equals null

Not the prettiest logical statement, but much faster :).

coalesce() is a very cool function. I wish I found out about it earlier...

Use Batch Processing (Dataverse)

if you need to do something with every record in a table, try out batch processing. Needless to say, be wewy wewy caweful! Try it out with non-productive data first! Your flow may complete in a few seconds, where it took minutes before (with Apply to each).

Help from LLMs

Don't expect your LLMs like ChatGPT to give you speed-optimized solutions. You need to specifically prompt for fast solutions and even then a response may be very slow executing. Sometimes a "Hmm, this seems a very slow and inelegant solution. How can I speed this up" may help a little, but most of the time (at least in 2025), you'll have to implement these fast solutions yourself. But you probably already found out the hard way, that none of the current LLMs are particularly well suited for Power Automate. I think, in a few years from now, this may well be the death sentence for Power Automate, unless Microsoft finds a way to improve Copilot. As of today, writing code with any of the coding LLMs is light years ahead of what Copilot can do for Power Automate users....

Apply to each on a string with delimiters

A seemingly elegant shortcut

You have a string called myString that contains IDs, delimited with commas: 103,412, 422,781. 

Now you would like to do something with each of the IDs inside an Apply to each loop. In theory it sounds like a very efficient idea to use the following expression to create an array right where you need it - in the input variable field of "Apply to each":

split(variables('myString'),',') 

The caveat

What if the string is empty (aka null)? The function split will fail, because it expects a text as its first argument.

The solution: try and make sure that myString is at least an empty string, using coalesce:

split(coalesce(variables('myString'),''),',')

Another caveat

The flow is still failing (sometimes), what the hell! The issue here is that you are now splitting an empty string into an array. And the output of this isn't simply an empty array, but an array of size 1, where the value of the first element is an empty string.

[""]

So your "Apply to each" loop runs 1 time even though it was supposed to run 0 times😡. 

In an ideal world, the following expression would return an empty array - so it can be used for "Apply to each":

split(null, ',')

Sadly, it doesn't. 

What we need to do is to explicitely create an empty array, using json('[]'), should the input string be null:

if(empty(variables('myString')), json('[]'), split(variables('myString'), ','))

Can't deploy your solution using a Power Platform Pipeline due to Dataverse dependency issue?

The Symptome

Your deployment fails with an error message like this:

ImportAsHolding failed with exception :The SavedQuery(...) component cannot be deleted because it is referenced by 1 other components. For a list of referenced components, use the RetrieveDependenciesForDeleteRequest.

The Cause

In your deployed solution, you removed a component. This could have been a view, a form, a column, etc. In your DEV environment it seemed straight-forward. You checked, that no other component in your solution is using the unwanted component and when there were no more dependencies, you removed the component from your solution or worse, you deleted the component from your DEV environment.

The issue is that the Power Platform Pipeline is not as smart about the order of operations. It wants to delete the component before it has been cleared of the dependencies in the target environment - so exactly the opposite of what you did on your DEV environment. And of course since the dependency is still present, it can't delete the component.

How to Fix: Remove the dependency by hand on the target environment

If you simply removed the component from your solution on DEV, then re-add it too your solution and follow the method below ("How to Avoid"). If not, you need to remove the dependency by hand on your managed target environment(s). Here's how you can do that.

Deactivate "Block unmanaged customizations" in your target environment (Power Platform Admin Center - Settings - Product - Features).

Open the "Default Solution" on the target environment and find the component you'd like to delete. Use the tree dots next to it to show the dependencies.

Then also uses the "Default Solution" to navigate to all the components that still have your unwanted component and remove the dependency by hand. 

Do a sanity check again and see if the unwanted component has no more dependencies listed. Publish all customizations.

Now re-deploy the solution from your DEV environment.

If all went smoothly, activate "Block unmanaged customizations" again. 

How to Avoid

Generally, be cautious with "delete from environment" on your DEV environment. It's better to execute such removals in two steps. 

A) Deploy a version with the component still part of the solution, but all dependencies removed to it (by other components). Don't forget to publish all customizations before deploying. After deployment, confirm that the component has indeed no more dependencies on the target environment.

B) Remove the component from your solution (you can now safely delete it from the environment, if you are certain you'll never use it again). Publish all customizations, then deploy again.

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.