MS Forms for Power Platform solutions

 The Core Issue

1. You follow the best practice of working with three environments: DEV, TEST and PROD

1. MS forms cannot be deployed as a solution component

1. Therefor changes you make to the forms during development (DEV) immediately affect the PROD environment as well.

1. Bad things happen

The Core Solution

While it is not possible to make MS Forms directly "deployable", you can create two separate instances of MS Forms.

One for PROD and one for DEV - and, optionally, a third one for TEST.

But now, your flow won't know which form to pick. The actions "When a new response is submitted" and "Get response details" now need to access separate forms...

As always, in these situations, you define an Environment Variable in your solution:

• Display name: "Forms ID"
• Data Type: "Text"
• Current Value: the long id string you can find in the URL of your MS Forms.
  It's at the end of the URL, after "id="

Example: 2zmkx2LkIkypCsNYsWmAs16f82eqhpRLv2fAqFaQjmBURItZR1lIQVAzWlBUQkhNQUJJQzNVRFBTUSQlQCN0PWcu

(Or course your DEV forms and your PROD forms, will have different IDs!)

Two forms = Two sets of internal QuestionIds

If your PROD form is simply a copy of your DEV form, all the QuestionIds may initially be identical. However, you can't count on it. As soon as you start working with these two forms independently (e.g. during a later development cycle) you will get separate QuestionIds. 

We need a way to map the question to the QuestionId for each environment. For this, let's create a list of key/value pairings in a new Dataverse table - where the key is an abbreviation of the question, and the value will be the target QuestionId (of the environment-specific MS Forms instance).

Create a Dataverse table; call it something like "Forms Mapping".

Leave the default "Name" column, we'll use it as the key in our key/value pair.
Create a Text column called "Value", that holds the QuestionId

For each question in your forms, add a record to this new table. Example:
Question: "What's our email address?" -> name = "email", value="_email_question_id_here_"
Question: "What's your date of birth?" -> name = "birthdate", value="_date_question_id_here_"

Okay, but where do we get the actual QuestionIds for each form?

Luckily, that's quite easy, if you set your MS Forms to Preview mode and view the page's source code. If you are using Google Chrome and hover over the source code, it will help you navigate quickly through to the elements that hold the answer. 

You are looking for <div data-automation-id="questionItem" ...  and inside it, you will find the QuestionId on the next source code line; e.g. id="QuestionId_r334f23da2...". Copy the id (without the prefix "QuestionId_") into your Dataverse value field. Repeat for all questions. 

Since the forms are not hardcoded anymore into the flow, we need a way to access each question, using the QuestionId. For this we will convert our Table "Forms Mapping" into something your flow can work with.

1) List rows (Dataverse)
Select your Name and Value columns (by their logical names). Sorting is not really necessary.

2) Select

From:
All items from the "List row" action (Dynamic content name = "value")

Map:
item()?['xyz_name'] 
(use your own prefix)
to:
outputs('Get_response_details')?['body']?[item()?['phfd_value']]

3) Compose
first(json(replace(string(body('Select')), '},{', ',')))

Now whenever you need access to a specific response of the form, don't use dynamic content. Instead, use the expression:

outputs('map')?['_question_key_here_']

Example:
outputs('map')?['birthdate']

This will resolve the key (or name) to the matching value (QuestionId) using your Dataverse Table "Forms Mapping" and use that Id to access the form's response to that question.

Lastly

There are two QuestionIds you don't need to worry about, because they are always the same:

"responder": E-Mail-Adresse of the responder (only for members of your tenant!)

"submitDate": Date of submission

For these you can absolutely use dynamic content safely. 

I hope this approach works well for you. Good luck!

Insanely fast synchronization from a Dataverse table to a SharePoint List

Don’t be fooled: this challenge is deceptively hard — at least if you want to make it fast, efficient, and robust. 

Feature specs

Your source of truth is a Dataverse table. But since not everyone in your org has the necessary license or access rights, you would like to also show the content of that table in a SharePoint list.

• Develop a synchronization flow that ensures the SharePoint list always reflects the current state of the Dataverse table.
• This synchronization should run once per day (to limit the number of flow runs), for example nightly.
• The SharePoint list is read-only for everyone (except your flow).

The usual approaches (not recommended!)

• You delete the whole SharePoint list content every night and completely refill it with the Dataverse content. This is extremely simple and extremely wasteful in terms of flow calls and SharePoint resources. You also lose the version history, which could be useful to your users.
• Each night, you iterate over every row of your SharePoint list and compare it to the Dataverse table. Very inefficient and very slow, but at least the version history is maintained. 

A much better approach (very fast, efficient and robust!)

Your SharePoint list

... should have two additional columns, that will make the whole sync process possible:

• "Key" (Text): The GUID of the Dataverse record, which is the source of truth for the corresponding SharePoint row. Active "Enforce unique values" on this column. If you are familiar with databases, this change will effectively allow this column to become an alternate key.
• "Last sync" (Date/Time): Timestamp indicating when this row was last updated by the flow.
• Make sure this list is read-only (except for your flow)

Flow structure overview

1. Load all needed data once: Load both the full Dataverse table and the SharePoint list once—right at the beginning of the flow.
2. Find records to sync: Find records to sync: Use Select, Filter array, and some additional trickery to identify Dataverse records whose corresponding SharePoint row is outdated or does not yet exist.
3. Create or Update: Iterate over the content of the filter array. If a SharePoint row does not exist yet, then create a new one. Otherwise, update the row. 

Part 1 - Load all Data

This one is straightforward, simply load your complete dataverse table and SharePoint list. The only caveat is that you will run into issues, if your SharePoint list is over 1000 entries. Activate pagination on the "Get items" action, just in case. Set it to 500. 

You could also filter out inactive Dataverse records, but I would advise against it. You’ll see in Part 2 that the approach is so efficient that excluding records provides no measurable performance benefit. 

Part 2 - Find records to sync

This is where the magic happens. I broke it down for you in three essential steps (2.1 Select, 2.2 JSON Object, 2.3 Filter Array).

Part 2.1 - Select

Based on the SharePoint List, create a JSON array (using Select), with the Dataverse GUID (column "Key") as the JSON key and the column "Last sync" as the JSON value.

Select Parameters

From: value from "Get Items"

Map: 

• Key:   item()?['Key']
  
• Value: item()?['LastSync']

This will create an array of JSON Objects that look like this:

[

{"9eb87b2e-a8cb-f011-8544-000d3a8328a0": "2026-04-23T15:59:53Z"},

{"1cc55b01-0ad5-f011-8544-000d3a8328a0": "2026-04-23T12:45:44Z"},

{"077a4688-5e5d-f011-877b-002248db2a54": "2026-04-23T12:45:49Z"}

]

This is not yet what we need. We need a single JSON object with the Dataverse GUID as the JSON key, so we can

perform a standard JSON query on it in order to access the "Last Sync" timestamp.

Part 2.2 - Create a flat JSON object

Flatten the output array from the Select action into a single JSON object. This can be done with nested replace functions, although the expression looks a little bit horrifying :D.

json(replace(replace(replace(string(body('Select')),'},{',','),'[',''),']',''))

Inside a Compose action, this expression does the following steps (from inner to outer function):

1. Turn the Output from Select into a string
2. replace all },{ sequences with a single comma, effectively dissolving the boundaries between the JSON objects in the array.
3. turn the whole string back into a JSON object, using json()

We are now left with a unified JSON object, that looks like this:

{

"9eb87b2e-a8cb-f011-8544-000d3a8328a0": "2026-04-23T15:59:53Z",

"1cc55b01-0ad5-f011-8544-000d3a8328a0": "2026-04-23T12:45:44Z",

"077a4688-5e5d-f011-877b-002248db2a54": "2026-04-23T12:45:49Z"

}

We can now query this json object "What's the last time the row with the Key XYZ was updated?". We need this for our next step.

Part 2.3 - Filter Array

Filter the Dataverse records using a Filter Array where the modifiedon timestamp is greater than the Last Sync timestamp of the SharePoint item with the same Key. 

From: value from the Dataverse table ("List rows" action)

left:    coalesce(item()?['modifiedon'], item()?['createdon'])

operator: is greater than

right: coalesce(outputs('Compose')?[item()?['abc_uniqueid']], '2000-01-01T00:00:00Z')

• The first coalesce() is used in case modifiedon is empty. It will then use "createdon" as fallback.
• the second coalesce is there in case our flattened json object does not have a key, we are looking for (in other words, the SharePoint list row does not exist yet). If this happens, we should use an arbitrarily early date, which is guaranteed to be lower than any of the dates in our Dataverse table's "modifiedon" column. January 1, 2000 is a safe choice for this purpose.

Part 2.4 Almost done!

We now have the output of this filter array, which is essentially a list of a Dataverse table records that should be updated in the SharePoint list. The beauty of this approach is that Power Automate can perform all of this in a fraction of a second. No "Apply to each" so far!

Part 3 Iterating over the filtered array

Okay, but now what, we know which Dataverse records need to be updated, but how do we know which Sharepoint row corresponds to each Dataverse record?

The fastest method would be to essentially do the exact same thing again, but this time create a flat JSON object where the key is still the Dataverse record GUID, but the value is the ID of the Sharepoint list item. 

Select 

From: value from "Get Items"

Map: 

• Key:   item()?['Key']
  
• Value: item()?['ID']

Compose

json(replace(replace(replace(string(body('Select_2')),'},{',','),'[',''),']',''))

We now need to use "Apply to each" for the first time in our flow. There is no way around it. We iterate over our filter array and do the following steps:

1. Use a compose action to get the SharePoint list ID from a given Dataverse GUID.
2. If this ID does not exist, it means the row does not exist and therefore we have to create it
3. If it does exist, we can Update the list item

Limitations & scale considerations

This approach is intentionally optimized for small to medium datasets, where performance, robustness, and simplicity matter more than horizontal scalability. Because the flow loads the entire Dataverse table and the SharePoint list into memory at the beginning of each run, it relies on Power Automate’s ability to comfortably handle in‑memory JSON objects.

In practice, this works extremely well for hundreds to low thousands of records. Once the SharePoint list grows beyond a few thousand active items, memory pressure and payload size limits in Power Automate can start to become a bottleneck. At that point, the bottleneck is not SharePoint or Dataverse themselves, but the fact that Power Automate evaluates all Select, Filter array, and expression logic in memory rather than streaming records.

Another important consideration is data shape stability. This pattern assumes:A stable one‑to‑one relationship between Dataverse records and SharePoint rows
A guaranteed unique key (the Dataverse GUID)
A clear definition of “last updated” that is fully controlled by Dataverse
If users are allowed to edit the SharePoint list directly or if multiple systems write to the same list, the assumptions behind the timestamp comparison no longer hold.

Finally, this approach trades absolute real‑time accuracy for efficiency. By design, synchronization happens on a scheduled basis (e.g., nightly). If near‑real‑time synchronization or very large datasets (tens of thousands of rows) are required, Dataverse change tracking, event‑driven flows, or per‑record lookups should be considered instead.

In short: this pattern shines when SharePoint is a read‑only projection, the dataset is bounded, and the goal is to achieve maximum speed with minimal flow actions - not when SharePoint is expected to behave like a transactional replica.

Nuances

• Consider filtering both your Dataverse records on load as well as your SharePoint list items. Use an identical filter in both cases! Example: {all active records} OR {those who have been changed in the last 100 days}. This will disregard inactive records that have been inactive for enough time (100 days) so that their state switch from active to inactive could be synced by your flow.
• Consider creating a filtered array of all the SharePoint items whose Key does not exist as a GUID in the Dataverse records. In other words, the record was deleted from Dataverse. Then iterate over that array and delete the rows. You can do this with a Select on the Dataverse records ("All Guids"), with the Map being only the GUID. Then Filter Array on the SharePoint items and the condition being: "All Guids" does not contain the value in the Key column item()?['Key']. 
• In case the needed information to build the SharePoint list item is spread across multiple tables, do consider using FetchXML or Expand Query. I personally love working with FetchXML and use XrmToolbox to develop and test a new query before dropping it into Power Automate.
• You can further speed up your flow by activating concurrency on the "Apply to each" loop that iterates over the filtered arrays (create/update or deletion).

Performance benefits

My flow works with around a hundred test records. And has now a runtime between 600 ms and 4 seconds (worst case). Without these optimizations, the flow ran for about 30-40 seconds with a much higher amount of API calls.

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. 

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. 

Welcome to "Professional Power Automate"!

 Hi everyone and welcome to the "Professional Power Automate" blog. When I was learning Power Automate, I often ran into the issue that there are hardly and blogs on more advanced topics, like "what are best practices to create robust flows" or "what should I consider when deciding a complex system of child flows?". Writing blog posts is also a great way for me to collect my findings and bring them into a more concise form. In that regard, writing is also part of my own learning journey. I also would love this to be a community effort in the sense that we all have different approaches and practices. So, your comment is always welcome. However, all comments are moderated to avoid spam and off-topic discussions. Please be patient, if it takes a couple of days for your comment to pass moderation :).

What this blog is for

• Designing complex flow systems
• Best practices
• Advanced discussions
• Hardening flows
• Premium license aspects and workarounds
• Creating more efficient and fast flows
• Helper tools that make designing and developing flows easier
• Dataverse tips
• Speeding up your workflow

What this blog is NOT about

• off-topic discussions, i.e. anything outside of the Power Automate and Dataverse
• not primarily a support blog!
• Eventhough the name may suggest it: Power Automate for Desktop is considered off-topic (at least for now)

About me

My name is John Flury and I've been working in IT here in Zurich, Switzerland for the last 17 years, after studying computer science at the University of Zurich. My programming background is in Java and C/C++, but I was never a full-time developer. As an IT manager, my focus have been mainly business processes. As such, it's no surprise that Power Automate caught my attention. Since about 2 years I teach Power Automate beginner classes and also work as freelance Power Platform consultant and developer. In my freetime I enjoy being a "Maker", creating interactive installations for kids and build things in and around our house, using Arduino, 3D-Printing, wood and metal.