We have all been there, we need to check the difference between 2 dates, and if you ever had to implement this you would need to use some crazy mathematical equations using the ticks() expression. But now..
I’m not sure when this expression got added, but we can now use dateDifference() expression instead of using ticks().
The dateDifference() expression is a powerful tool in Power Automate and Logic Apps for calculating the difference between two dates.
Allowing to easily determine the number of days, months, or years between two dates, which can be useful in a variety of scenarios.
The result is in the format of: Days.Hours:Minutes:Seconds
Note:: If the dates passed in have no time interval, the result shows zeros for the hours, minutes, and seconds. We can extract the different parts of the return by using some expressions inside a Compose action, which we will do next.
Extracting the Result
If you need to extract certain parts of the result into the hours, minutes, or even seconds, you can use the split() expression. Below you will find the explanation on the extraction, as well as the exact expressions to use.
The split() function splits the output of dateDifference() at the period (‘.’) into an array with two elements: days and the rest (hours:minutes:seconds).
The [0] indexer retrieves the first element of the array, which represents the number of days.
The int() function converts the days from a string to an integer.
Is your IF condition always evaluating to False? Debugging and Testing your Flows should be easy. When using a Condition in Power Automate, in the run we cannot see the expression or the results of what is being evaluated.
I will go over a quick workaround to debug and find out what is happening in the condition
The Problem?
Is your Condition not working as expected? The problem is when we use a Condition action inside Power Automate, we cannot see the “equation” that is being evaluated when looking into the run.
The problem affects how we can troubleshoot, the following solution will show what is happening inside the Condition action during the run.
Scenario
In this scenario, I am checking: If one value is greater than a second value
Now during a test run, I expect to see this condition true, but in my run it is always showing false and going in the If no branch.
The big problem is though, I cannot see what the values being evaluated look like. Take a look below
Clicking on the “Show raw inputs” is also not helpful..
Solution
So what is this quick and easy solution to see the condition results? A simple ‘Compose‘ action.
Lets take a look: First add a Compose under your Condition
Next copy the values that are in the Condition to the Compose. My Compose now looks like this:
Now make sure the Compose is above your Condition. I am just dragging the Condition below the Compose
Next, we can run the Flow again, and see what the Compose can tell us:
Yikes! We can see our 2 values that are being evaluated are both 15. And 15 is not greater than 15. This is why its returning false.
My Thoughts
In my opinion, this should be already visible inside the Condition action. To get this feature added to Power Automate, we can vote on this feature. Head over to the Community Forum and vote for this idea.
I needed to check if an HTML table had data or not. Usually when I need to check I have two expressions I like to use: ’empty()’ or ‘length()’.
The Problem
I needed to check if an HTML table had data or not. Usually when I need to check I have two expressions I go to first.
empty()
length()
I tried using empty() and found that the HTML table even when empty, is not truly empty. I then tried length() and found that when the HTML table is empty there is still a length of 30.
The Scenario
I have some data that is used to track different devices that can be loaned out. The data has properties like, Type of device, Serial Number, Etc.
I want to put this array of data inside a HTML table and send it out on an email. The problem is, my data might be empty, as only available devices will show up in my data.
I need to check if the HTML table is empty, if it is empty: If True: Send email with HTML table If False: Send email without HTML table
The Flow
For this Flow, I will be using an Array Variable to simulate my data coming in from another system. I will call this Variable ‘Data‘. The HTML table action will be added underneath. You will need to determine if you want to use ‘Custom columns‘ or ‘Automatic columns‘ This can be done in the advanced options in the HTML action:
My ‘Data‘ Variable is empty at the moment. This is what we want for our first run, we want to get the length of the HTML table when its empty.
Next add a ‘Compose‘ action, and use the expression length(), pass in the HTML table as the parameter. For example, my expression looks like:
length(body('Create_HTML_table'))
Now run the Flow with no Data in the HTML table, and check your Compose action to see what the length is. In my case it is 30
Now we can add a If Condition to check if the length is greater than 30
** TIP ** I am passing in the Compose action into the condition, this allows me to see what the outputs of the Compose action before it gets evaluated inside the condition. This is extremely useful for troubleshooting
Conclusion
The Flow will go into the ‘If yes’ block if the HTML table has data
The Flow will go into the ‘If no’ block if the HTML table is empty
Of course checking the Data Variable itself for length could work way better. This example is mainly for data that can come in that could have loads of junk. For example: An HTTP API could bring in no data, but still have other information attached like, headers, status code, version. In this case we can only do conditional checks on the HTML table, since our Data variable will always have something being passed in.
Power Automate Expressions – union
Using union to remove duplicates on two different collection arrays
This blog will be looking at an expression called union() This expression can be used to remove duplicates from 2 collections. In this example, I will be using an Excel file to demonstrate this. But this can be done with any data
The Scenario
I will be taking an Excel file that has many Paper Items.
We want to remove all the duplicate items from the Plastic Item column. Why would we want to do this? One reason may be that we get this file from a 3rd party, but we only care about the unique items to import into another system (CRM, CDS/Dynamics, SharePoint, Etc.)
Steps
First we want to add an Initialize variable, Type = Array Next, we need to get the data we want to use. In this example I am using List rows present in a table
Now we add a Apply to each loop, and use the dynamic content value from our Excel action Inside the Loop we add a Append to array variable action, and add the column we want to remove duplicates from. In this example Paperitem
We are done inside the loop..
Outside the loop we add a Compose action, this is where we will put the expression union In the Expression tab type union() Select the dynamic content tab and pass the array variable to union twice (Click your variable comma click variable again) Your expression should look something like this:
In this post, I wanted to share some of the tips and tricks that I have learnt and came across. Some of these tips I will cover the basic fundamentals so you can start using them to help you build you Flows with less pain and more fun!
1. Compose
Compose actions, are one of my favorite actions to use. They are so robust and can display pretty much anything. Compose can be found under Data Operation > Compose
1.1
Add a Compose action before a Condition to check what values are being evaluated. In this example, my condition is saying: If length of value is greater than 0 I add a Compose action before the condition, with the same value I am using in the condition. This allows me to check the run and see the value being evaluated Example:
1.2
If Flow is spitting out errors about a wrong data type or you keep getting unexpected values – Since Compose can hold just about any data type. We can add one and check what the value is in run history.
1.3
Compose actions are great for doing expressions – We can add a Compose action and do expressions in the input, instead of writing the expression directly inside another action. This is handy if you need to do multiple expressions, or error checking.
2. Meaningful Names
There is nothing worse than leaving the default names for all your actions. This can be confusing, which can cause errors which could have been avoidable. This tip is going off tip #1.
2.1
The picture below shows a bunch of Compose actions Left: Default names used – Makes it very hard to know which action is what Right: Custom names used – Changing the names to something meaningful allows actions to be easily found and referenced.
2.2
For awhile now, Power Automate has had a limitation with renaming actions that were being referenced in other places. That has since changed. We can now rename action that are being referenced in other actions! For example: The left picture shows the Compose action named ‘Compose Project’ and is being referenced by the condition below. Changing the Compose action name to ‘Project Name’ also changes the name in the referenced condition, which is shown in the right picture.
—–NOTE —– Limitations still exist.. The reference will not be updated if the action is in a expression OR inside a Loop. And will throw an error of: Template Validation Failed. So it is always best to rename actions right away —————
2.3
When using Expressions, to better understand and show others what is going on. Comments are great for this. For example, if using a complex expression I like to copy the expression and put into a comment of that action:
3. Scopes
Scopes are handy, they can be used for grouping actions together. In this guide I will show how to use scopes to group actions together. There are many advanced techniques that scopes can be used for, but for this demo I will be showing the very basics. Scopes can be found under Condition > Scope
I will add 2 scopes, one for Users, and one for Accounts To add actions in the scope, Click and Drag the action into the scope
Drag the actions in so the order of the actions remain the same. For example, I put the Scope above the List Records action so I could drag the List records in first than the For each loop next
This concept will help us in the following tips
4. Parallel Branches
All Flows run from top to bottom in a single order fashion. If we split the actions to run side by side (parallel) This would drastically improve performance. In this scenario I have Two(2) CDS List records, and Two(2) Apply to each loops
List Records – Users Apply to each – To Append(add) all First names into a single variable List Records – Accounts Apply to each – To Append(add) all Account names to a single variable
Here is what the Flow looks like:
Running the Flow, we can see it takes about 4 minutes
Before we create a parallel branch we first must put the actions inside a Scope, this is to allow us to drag the actions around without getting the
This action cannot be dragged above actions it depends on. Error
Refer to Tip# 3 for details on Scopes
Now lets have these 2 different Scopes run in parallel branches First we want to click the ‘+’ icon where we want to add the branch, next click Add parallel branch
In this example, I am adding a parallel branch above both Scopes
Our screen will look like this:
Next we need to Click and Drag one of the scopes to the new branch
Once the Scope is in the branch we can click the X on the Choose an action tab
Now lets run thew Flow again: As the picture below shows, the Flow now only takes 2 minutes to run, cutting our run time by half, that’s insane!
Imagine our run normally taking 30 minutes, now only taking 15, just from a simple step
Tip# 5 will make our Flow run even faster!!
5. Concurrency
What is it? By default loops run one after another. Concurrency can make loops run in parallel. To better understand this, we can think about a grocery store. In this scenario:
People = Value that is going into Apply to each loop Lanes/Cashier = Concurrency Control Number
There are 20 people waiting in line at the store. There is 1 lane/cashier open. Since the cashier can only take one person at a time, this process can be lengthy. Now with Concurrency turned on, things are a bit different. Lets say we set Concurrency to 5. This means 5 lanes/cashiers will be open. This is great! But there is a catch..
When Concurrency is enabled, there is no more line. All 20 people are processed randomly.
Concurrency can be applied to all Apply to each loops — As well as Triggers!
To enable Concurrency:
First, click the 3 dots on the Apply to each action Next, click settings
Next, choose the value for Concurrency and click Done
For this Demo, I am setting Concurrency to 50. Please be aware of API limit calls and 429 errors. If you get these errors while flow is running. Decrease the Degree of Parallelism
Now lets see this in action! I set Concurrency to 50 on both my Apply to each loops from the last Tip. As a recap, the last time we ran the Flow, it took 2 minutes to run.
Results: About 5 seconds!
The Run time is about 5 Seconds. Since we used a Parallel branch the total run time is the longest duration of our Parallel branches
This drastically improves performance.
Conclusion / Key Take Notes
Use a Compose action to check values and outputs, that you normally cannot see, Like in a If Condition
Compose actions can store almost any data type. When in doubt.. Use a Compose
When using expressions, try using them in a Compose, this can make troubleshooting much easier
Always use meaningful names for all your actions. Doing this will save loads of time later on, when needing to use dynamic content
Comment, comment, and more comments. All actions allow for comments. This will help support teams troubleshoot your Flow, as well as your future self, if you have to make any updates later on
When using expressions, copy the expression and paste into the actions comments section.
Use Scopes to group actions together.
To speed up performance, use parallel branches to enable actions to run side by side
Enable Concurrency control on Apply to each loops to drastically increase performance
Remember enabling Concurrency control randomizes the order the loop iterations run in. So if you’re expecting a certain order processing to happen, do not enable
These are some of the tips and tricks I wish I knew sooner. I hope at least one of these tips and ticks have helped someone. Thanks for reading!
We will be adding a Security Role / Field Security Profile to users in CDS. For this demo, our scenario will be grabbing all the users from a Office365 group and assigning them a certain Security Role / Field Security Profile.
The source of the users can be from anywhere: – MS Form – SharePoint – Array inside the Flow – Excel Table – AAD Group / Office365 Group
Prerequisites
We will be using the Common Data Service Current Environment connector. This means that our Flow, MUST be created inside a Solution.
You will need appropriate permissions to be able to assign Security Roles and Profiles to
Steps
INFORMATION: This Flow will work the exact same to add Field Security Profiles instead of Security Roles. The only changes you have to make are in the List records – Get Security Role, and the Relate records – Security Role to User. The changes are listed in the captions of those images.
We use a Variable to store the name of the Security Role we want to add to the users. Than use a List records action on the Entity Security Roles In our Filter Query we will use: name eq ‘ ‘ Since we are using a variable to store the name of the Security Role, we pass this into the Filter Query
Field Security Profile = Change Entity name to Field Security Profile
Next, add a Compose action, to get the Odata URL. This URL is how we will add the Security Role to the User later on.
1) Inside the Compose action select Expression tab 2) Use the expression first() 3) Click back to Dynamic content tab
We use first() to get the first value in the CDS List records action. This allows us to bypass the Apply to each loop that Flow creates for us
4) In the ( ) select the Dynamic content value from the List records action
TIP: Make sure you see the fx logo in the text box, this indicates we are using an expression
5) At the end of the expression add:
?['@odata.id']
6) Click OK
7) Confirm the expression saved correctly by hovering your mouse over the expression
Next, use any data source / connector that meets your needs to get the emails of your users that you want to add – In this example I am using Office365 List group members
Add an Apply to each loop – So we can loop through each email and assign the Security Role
Inside the Apply to each loop, add a List records action on the Users entity Filter Query = internalemailaddress eq ‘ ‘ Add your dynamic content that has the email address for the user to add inside the ‘ ‘
Next, add a Compose action – to store the User ID (Unique ID) We use the same technique as mentioned above, using first() and the field name Add this to the end of your expression
?['systemuserid']
systemuserid = the field name in CDS that stores the Unique value for each user. This value is used as a lookup guid. So we can relate the records to this guid
Still inside the Loop: Add a Relate Records action.. This is one of the actions inside the Common Data Service Current Environment Connector. Entity Name: Users Item ID: The Compose – Get User ID Outputs Relationship: Select ‘Security Role – systemuserroles_association’ from the drop-down URL: The Compose – Security Role odata URL
Field Security Profile = Change Relationship Dropdown to — Field Security Profile – systemuserprofiles_association
Your action should look like this:
Conclusion
Adding Security roles or Field Security Profiles, can be a long and tedious process. You can add this Flow to a MS form and have users fill out what roles they need.
Have you ever noticed that Power Automate will sneak in that ‘Apply to each loop’ even though you are only expecting a single value.
For example this can happen whenever you are using a Action that ‘Lists’ items, folders, fields, etc.
In this example I will be showing how to get a User ID in CDS with the users Full Name.
Have you ever noticed that Power Automate will sneak in that ‘Apply to each loop’ even though you are only expecting a single value. For example this can happen whenever you are using a Action that ‘Lists’ items, folders, fields, etc. In this example I will be showing how to get a User ID in CDS with the users Full Name.
The Problem
Power Automate creates a ‘Apply to each’ loop when selecting dynamic content from a action that Lists items, folder, or anything. In most cases this is awesome and creates a nice smooth workflow.
However, what about when you know exactly what you want to look for and you know its only going to be 1 record, item, whatever is going to be returned. Power Automate will still make you use the ‘Apply to each’ loop.
This may not be a such a terrible thing, but if you need to do multiple things underneath that action, you will have to put them in the loop as well (if you need any data or reference to that action)
The Solution
On to the magic.. In my example Flow I will be using:
‘Compose’ action to have my Full Name stored.
‘List records’ CDS action to list records from the default Users table entity. ** Note – This can be done with any connector. **
ODATA filter on the ‘List records’ which I am using to filter ‘fullname’
Under the ‘List records’ I use a ‘Compose’ action to store the users ID(Primary Key from CDS) and the users Email Address
Step 1 – I am using CDS List records for my example, with a Odata filter
My List Records with my Odata filter
Step 2 – Adding Compose action to use the Expression to bypass the loop
Add a Compose action below the List action. And select Expression
Type anything, this is to keep us in Expression mode when we switch back to Dynamic Content tab
If you see the fx Logo in the Dynamic Content Tab, you have done this correctly
Remove what you had, and Click the Value of the action you want to bypass the loop with
Remove the ? and add [0] This is saying we want the first record only. Since this returns an array we say 0 as this is the first record in an array
after the [0] we type what the field name is, in this format: [‘feildname’]
Click OK.. I usually like to put the Expression in a Comment
This is the exact expression I used in my Compose action
body('List_records')['value'][0]['systemuserid']
DONE!
Limitations:
The only thing you have to watch out for is when there is a empty record. This will cause an error if the record is empty. This can easily be fixed using a Condition If block before the Compose to check if value is empty using the empty() expression. OR If you want to avoid the error altogether, you can use the expression first() instead of body()
I hope anyone finds this useful. This boosts performance greatly when you only need one record since you wont need a Apply to each loop.
![Flow Alt Delete – Josh Cook [Microsoft MVP]](https://flowaltdeleteca.files.wordpress.com/2020/04/cropped-img_0521.jpg)
Flow Alt Delete - Josh Cook [Microsoft MVP] 