Wouldn’t it be nice if we can Test our Flows without executing some of the actions like Sending Emails, creating items in SharePoint or Dataverse?
Guess what we can! And its very easy to do. Check this out!
If your like me, you test your Flows over and over again. This results in sending unwanted emails, creating items in SharePoint or Dataverse, Creating files on OneDrive or SharePoint. Every time you test your Flow, these actions inside our Flow get executed and cause unwanted behavior when Testing.
Wouldn’t it be nice if we can Test our Flows without executing these actions? Guess what we can! And its very easy to do. Check this out!
For example, I have a Flow that Create a new row in Dataverse, and then send an email to the person who created the new row. That is fine, but what happens when we have other actions in our Flow that we want to test to make sure they are correct. I may want to test the Flow multiple times if I am doing some data manipulation, but this will result in Creating multiple unwanted rows (records) in Dataverse, as well as send emails every time.
We can clean up the testing process easily.
We can utilize a feature called Static Result.
First click the 3 dots on the action, and select Static Results.
Next we can configure the static results. For easy example click the radio button to enable, select your Status, and the Status Code.
Now the action will have a yellow beaker, indicating that the action is using Static results.
Things to note: – Static Result are in ‘Preview’ so it could change at any time – Not all actions will be able to use them – If the option is greyed out, and you’re certain the action is able to use it, save the Flow and re open
This is only the beginning, as you can create a custom failed response, or create any result you want. This can help troubleshooting and testing certain scenarios.
REMEMBER!! To turn off static results when you want to execute the actions like normal.
Some examples on when to use static results:
Flow runs without sending emails
Flow runs without Approvals needed
Flow runs that need to test errors on certain actions
Uploading data from Power Apps can be scary on a security standpoint, since the user will need access to the Data Source. Lets use Child Flows to get around this, and use any connection we want.
You may have run into an issue when creating Power Apps that needs to submit data to SharePoint, Dataverse, etc. But did not want to give everyone in the app access to these. The problem is, Power Apps uses the connections of the user using the app, meaning if the app writes to a SharePoint List, the user will need Read/Write access. The same goes for Power Automate if we try to send the data to Power Automate from Power Apps, it still uses the users connection who triggered the Flow. How can we get around this? Read below!
If you block the HTTP Request connector via data loss prevention (DLP), child flows are also blocked because child flows are implemented using the HTTP connector. Work is underway to separate DLP enforcement for child flows so that they are treated like other cloud flows.
The Flows must be created inside the same Solution, so a Dataverse database must be configured on the Power Platform Environment
In this scenario, I will be showing how a user can use Power Apps to create items in a SharePoint List without being a member of the Site. This will allow us to use a specific Service Account to create the data in SharePoint without giving the user in the app any permission at all!
First we will build the Child Flow, then Parent Flow, and lastly customize the Power App
Inside your Solution create a new Cloud Flow.
For our trigger we use a Manual Button, and add the data we are expecting from Power Apps to put inside our SharePoint List (In my example I am only bringing in one field for Title)
Next, I add a Create Item action for my SharePoint List, and add the Parameters from the trigger inside the action.
Lastly, I add a ‘Respond to PowerApp or flow’ action, I create an Output called Success, and some details about what was created.
Make sure to use the Connection you want users of the App to use for the SharePoint Create item action.
Save and go back to the Flow dashboard screen (where you see the Details and run history screen).
There will be a Card on the right side called ‘Run only users’ click Edit
Under Connections Used, switch ‘Provided by run-only user’ to the connection you want to be used by users of the App (They wont have access to this Connection outside this Flow)
Now onto the Parent Flow
Go back to the Solution and Create another Cloud Flow.
For our trigger we use the PowerApps button trigger.
As best practice, create Variables for your data that is coming from Power Apps. Don’t forget to name them, as this will be the parameter name in Power Apps, Use the ‘Ask in PowerApps‘ dynamic content for your variable values.
Next we use a action called ‘Run a Child Flow’ (If you do not see this action, your Flow was not created inside a Solution) Add the parameters (these were the input parameters from the last Flow that we just created).
Lastly, add ‘Respond to a PowerApp or flow’ action. For this demo I am adding the parameter ‘Success’ this is from the child Flow.
Now onto the Power App, I am going to create a simple Power App with 1 TextInput for Title, and a Button to Pass the data to Power Automate. Here are my controls for reference:
For the Button: 1. Add the Flow to the button by clicking on the Button, 2. Clicking Action tab on top of page, 3. Clicking Power Automate 4. Select the Flow
Next add the parameters for the Flow, in my case I am adding the TextInput_Title.Text
Now, I want to add a Notification that the Item has been added, which will confirm my Flow has Run correctly. Ill be using the ‘Success’ Output parameter from the Flow for this.
To add this, I put my Flow run inside a Variable inside Power Apps. Ill call my variable Results, and IO add this to the OnSelect property of the Button where my Flow is:
Now I use the ‘Notify’ function to notify the user of the item being created, I add this after the semicolon. So my function looks like this in the end:
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
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.
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..
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.
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 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
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:
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
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.
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.
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!