Tag: Expression

Power Automate Substring and Text Positions Made Easy

Expressions can be confusing when starting out in Power Automate. Luckily the product team makes things easier each day. I will be showing how to grab an email address Josh.Cook@flowaltdelete.ca and transforming it to Josh Cook

Expressions can be confusing when starting out in Power Automate. Luckily the product team makes things easier each day. I will be showing how to grab an email address Josh.Cook@flowaltdelete.ca and transforming it to Josh Cook

The Scenario

For this demo, we will be formatting an email address and removing everything after the ‘@‘ symbol, to form a first name and last name.

We will be using Substring to achieve this. However we wont be using the expression Substring, we will be using a Action called Substring, this action can be found in the ‘Text Functions‘ connector.

The Flow

In my Flow I will use a Compose action to store an email address, this can be any data source or action that fits your needs.

In this example, we want to remove the At sign and everything after. To do this we could use an expression. But.. The Power Automate team has put together some actions that make Text Functions more easy.

At this time there is Two(2) Text Function action we can utilize.
1. Find text position
2. Substring
We will you both in this example

First we will add the ‘Find text position‘ action. This action has Two(2) parameters to fill in:
Text – The text we want to use, in this case we use the dynamic content of our Compose with the email
Search text – This is the text we want to find the position of

In the string Josh.Cook@flowaltdelete.ca the ‘@’ sign is in position 9. This is because in indexing values we count the first value as index 0

Next we add the ‘Substring‘ action. This action has three(3) parameters.
1. Text – The string of text to extract the substring from
2. Starting position – since we want to extract ‘Josh.Cook‘ our position will be 0
3. Length – This is how long the substring will be, so this is where we use the dynamic value from Text Position action (Which is 9)

Now when we run the Flow, we should take the value:
‘Josh.Cook@flowaltdelete.ca‘ And output ‘Josh.Cook‘

Mhm.. Not bad, now all that is needed is getting rid of the ‘.’ This can easily be done by using the replace() expression.
replace(<Dynamic Substring Body>,’.’,’ ‘)
The replace expression is replacing the ‘.’ with a white space char

replace(outputs('Substring')?['body'],'.',' ')

Now when we run the flow:

Conclusion

These new Text Function actions in Power Automate makes expressions even easier. I cannot wait to see what the Product group adds to these.
Thanks for reading, and as always please reach out to me on Twitter with any questions you may have. Thanks again!

Power Automate Expression Union – Return Unique Values

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:

union(variables('paperItemArray'),variables('paperItemArray'))

Done!!

Final Thoughts

Now we have a Array of all unique Plastic Items, which can be imported into another system based on your needs.

Thanks for reading!