Take a phone number like 4031234567 and turn it to (403)-123-4567 with one simple step!
Take a phone number like 4031234567 and turn it to (403)-123-4567 with one simple step!
We have a phone number coming in from a secondary system as a integer number. We than want to add some formatting to this to be easily read and look cleaner. For example, a phone number comes in like this: 4035557890 And make it look like (403)-555-7890
Things to Know
Since we add ‘-‘ and ‘( )’ this turns our data type to become an String. Keep this in mind, since you wont be able to pass this into a field in another system that is looking for a Integer value
This Flow is very simple. To achieve the formatted number I am using the action ‘Format number’. This is a fairly new connector that is mainly used to format currency values. But we can utilize the format to define any formatting we want
Looking at the above picture, we are passing a Integer phone number into the Format number action. Than we specify the format we want to use by selecting the drop down > clicking ‘Enter custom value‘
Since this action is fairly new, I am curious and looking forward to see if the Power Automate team will expand and add more actions like this to make formatting a breeze.
In the Excel connector we are forced to use the file picker (folder icon) to select a Excel file. But what if we did not know the filename, or we are expecting NEW Excel files.. We will need a dynamic filename.
I have learnt this method from John Liu. I have pointed so many people, and community users to John’s blog when they run into this Excel connector limitation. I have found multiple people thinking its too complicated, or confusing, because the method works with Graph API to get the File ID. So this blog will be a very easy guide to follow and hopefully will be able to get your Flow being able to use a dynamic filename. If you have any questions or problems with this guide or other Power Automate related questions, reach out to me on Twitter.
New Update – Easy Way 2021
The following video explains a way easier way to do this, no API, no messy File ID’s. Check it out!
Things to Know
To make this even easier, we will be having the Excel file that be stored into a SharePoint Document Library. We can also use the SharePoint HTTP call action to better build our REST call more easily. * We will still need the Excel file to have a table defined *
First we will create a new SharePoint Document Library for the files, or use an existing document library.
Next, we want to have one Excel file in the Library to start, so we can make sure this is working. Here is what mine looks like:
Overview of The Flow
For the Flow, I am using the SharePoint action ‘Send an HTTP request to SharePoint‘. This action uses MS Graph in the background, but makes it a lot easier to construct a API call. First we will use a simple call to SharePoint using the ‘Send an HTTP request to SharePoint‘ action to get all the Document Libraries for our site. Than use the output of that action to find the Document Library Drive ID. This is how we navigate through the SharePoint site, we want to do this until we are in the folder of our Excel File.
Lets Build that Flow
First we add ‘Send an HTTP request to SharePoint‘ action
In the SharePoint HTTP action, use the following: – Site Address: If your SharePoint site does not show up in the drop down, click use ‘Enter custom value’ and type the homepage of your site – Method: GET – Uri: _api/v2.0/drives/ – Headers: accept application/json
Now trigger the Flow to get the Output of the HTTP request.
I am using Visual Studio Code to paste the Output into, its free, and easy to use. If you have lots of Document Libraries, you may want to use a Find feature and search for the Document Library name. For this demo I will be searching Excel Files. We need to look for a specific ‘id’ inside the output
Copy this ‘id‘,we can use this value to navigate inside that Document Library in our HTTP request. Our new URI should looks something like: _api/v2.0/drives/b!MJxrwkJ2FUGanaxoijg0eZmnU9iP4aJJsidrZJQNlsBAb-8VLzYJRbD9CKmJa0j8
Now we need to add /root:/ to the end of our URI
We are almost complete! We now need to specify the path to our file. Here are some examples:
If file is directly in the Document Library(No Folders) – Just add the filename with the extension _api/v2.0/drives/b!MJxrwkJ2FUGanaxoijg0eZmnU9iP4aJJsidrZJQNlsBAb-8VLzYJRbD9CKmJa0j8/root:/MyNewExcelFile.xlsx
If file is in a folder – Add the folder name, than the Excel file with the extension * NOTE: If folder name has spaces, than use%20 instead of a space * Folder name = Historical Files _api/v2.0/drives/b!MJxrwkJ2FUGanaxoijg0eZmnU9iP4aJJsidrZJQNlsBAb-8VLzYJRbD9CKmJa0j8/root:/Historical%20Files/MyOldExcelFile.xlsx
Mine is not in a folder so I will be using the first one. Now to make the file dynamic, I will be using a Compose action to store the Filename. So in my HTTP Request I will be using my Compose action with the Filename and extension at the end. My example will look like this:
Now test the Flow to validate the HTTP request is valid. The output should have some references to the file, like the name of the file, size, created date time, and last modified date time. My output looks like this:
Next, to get the file ID, we use a Compose action with an expression. Add a Compose action, inside the Compose we will use the HTTP request Body dynamic content in a expression:
With the fx logo present on the dynamic content tab. Click the Dynamic content Body. Now we use JSON path to grab the file ID. This is done by adding a ?['id'] to the end of the expression. My expression looks like this:
body('Send_an_HTTP_request_to_SharePoint')?['id'] You can copy my expression, just make sure to change the Send_an_HTTP_request_to_SharePoint to your HTTP request action name
Next add an Excel Get tables action. Use the outputs from the Compose – get file ID for the File
Now add an Excel List rows present in table action. File: Use the outputs from Compose – get file ID action (same as we did for Get Tables) Table: Click Enter custom value. Use this expression to get the first table name first(body('Get_tables')?['value'])?['id'] Power Automate has been updated the new expression to get the tables is: first(outputs('Get_tables')?['body/value'])['id'] ** Same as before. Get_tables is my action name **
Now, sometimes the Dynamic content of the Excel columns wont be there now. You can easily grab any column you like using the item() expression. For example I have a column named Tasks so my expression will be item()?['Tasks']
Being able to use a Dynamic filename in the Excel connector can be tricky. But I am hoping with this guide, anyone who needs this business requirement can easily follow this, and get it done.
Using Azure Key Vault to pass in secrets into Flow for secure transportation of sensitive information
How are you using and storing sensitive usernames and passwords in Flow? We use usernames and passwords for so many different actions in Flow. One main use is in a HTTP action.
In scenarios like this, where do you store these usernames and passwords? This post will show how to use Azure Key Vault action to get a secret, which can be passed to any action securely using Secure Inputs and Outputs.
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
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.)
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!
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
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:
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.
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.
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.
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 —————
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:
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
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
Our screen will look like this:
Next we need to Click and Drag one of the scopes to the new branch
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!
Tip# 5 will make our Flow run even faster!!
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.
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!
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
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
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
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
4) In the ( ) select the Dynamic content value from the List records action
5) At the end of the expression add:
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
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
Your action should look like this:
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.
Basically, we want to be able to control if we want an error. At this time Flow does not let you easily throw an error.
There is a Terminate action which is awesome, I use it in every Flow or Logic App I create. The Terminate Action will terminate the Run immediately. Terminate will not allow other actions to execute, even if the ‘Configure Run After’ is set to Failed, Skipped.
This can be a headache trying to implement a way to easily Fail an action. While allowing the Flow to continue running the ‘Configure Run After’.
We will use a Try – Catch method. Basically we will use ‘Scope’ actions to house different parts of our actions. You can think of ‘Scopes’ like a way to organize your Flow. Add them into your Flow just like any other Action.
Try – Where your original flow design will be placed. Catch – This is where we ‘Catch’ – Meaning we will only run this Scope if the above Scope has a Failed Action
First add a ‘Initialize variable‘, define the type as Boolean, and call this variable Exception. Next add two(2) ‘Scopes’
Next we want to only run the Catch Scope, if the Try scope has failed. To do this, simply click the Three(3) dots on the Action and click ‘Configure run after’
Now deselect ‘is successful’ and select the ‘has failed’, and ‘has skipped’ checkbox. Click Done
Next, to trigger an error in the Try Scope, add a ‘Set Variable‘, and select the Boolean from above.
Now, inside the value use the expression null.
The Set Variable should look like this now:
Since our Catch block will run if Try fails, our run will show as Successful. We can change this by adding a ‘Terminate’ action
Add any other error handling you wish inside the Catch scope, some examples include:
Email to Support that Flow has failed
Different type of business processing
Delete a record
Rename a record
Service Bus – dead lettering
TIP: When adding other actions inside the Catch scope make sure to make the Terminate action last
Here is a Test run:
This is a great method for executing an error, Its fast and simple, and can be used many times in the same Flow. Thanks for reading!
Microsoft Forms is great for many use cases. However, with some use cases you may not want the whole organization to have access to this form.
In short, we want to setup a way to allow certain users to fill out a MS form. How can we achieve this? My Flow below will show how to take a list from SharePoint that has users emails, to only allow them to process the Form.
** Note – You do not have to use SharePoint, you can use any means necessary. **
First you will need to have a Microsoft Form created – Any MS Form will work
Next, we setup our List of ‘Authenticated’ Emails. Again in this demo I am using SharePoint I created a simple SharePoint List with a single text field to store email addresses
In the Flow, we create as normal with a Microsoft Form Trigger and action to get the Details Trigger – When a new response is submitted Action – Get response details Next add a Variable, we make the Type an Array Under the Variable, add Get items action, and choose our Authenticated users list that we created above
Next, add an Apply to each loop. Select the value from the Get items action. Inside the loop, add a Append to array variable, select the variable that we initialized above. Value = the column name that stores the email Address in our SharePoint List
** Note – Make sure the Initialize variable is type Array **
Outside the loop – Add a Condition Select the Array on the left side – Contains – Responder on the right
— We use Contains to check if the User filling out the form is in this Array of emails —
If the user who is filling out the form does not belong to the ‘Authenticated’ list, we Send an Email letting the user know they are unauthorized to fill out this form. Under the Send an email action, add a Terminate action with Status of Success, this is to ensure the flow stops running, while not throwing an unwanted Failed run
Here is what a User will see when they do not have access
I am sure there are a million ways to add your own intuitive Authentication methods. I hope my method helps or inspires someone else! Thanks for reading
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.
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)
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
Step 2 – Adding Compose action to use the Expression to bypass the loop
This is the exact expression I used in my Compose action
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.
Using the Filter or the Select Query in Excel is very easy to use. However the issues come up when there is a space in the column you are trying to Query, which results in a Bad Request error.
Using the Filter or the Select Query can be limited on the Excel connector. The issues come up when there is a space in the column you are trying to Query, which results in a Bad Request error.
A relatively easy fix would be to change the column name to have no spaces. Sometimes this is not viable or possible due to many systems talking to each other. Or perhaps a third party is supplying the Excel doc.
The fix in my example shows how to use the Select, and Filter array actions in Power Automate. Select is used to select certain columns to output. Filter Array is used to filter on certain conditions and values.
Step 1 – Add the Select action under the Excel List rows present in table action
Step 2 – Add Filter array action under the Excel List rows present in table action
Some Actions have a limitation on the Odata filter and Select queries. Some examples include: