When working with Power Automate we might be limited by the functions that are available to us, that we have traditionally used in programming languages.
Recently when working on a requirement that requires using distinct count of records for a certain property to perform some condition, I noticed that there isn't a function available to perform this.
To achieve this I had to perform the following steps
- Create select action to capture the column you want to target into an array
- Perform intersection function to get the unique records
- Perform length function to get the distinct count
Let's take a simple example to run through the above steps. I have created an array as shown below. I would like to get the distinct count of all the skills that are available from the below array
Next, let's create a select action to select only the 'PrimarySkill' column into an array
When we run our Power Automate we will see the new array output only the Skill column
For the final part, let's use a variable to capture the distinct count
If for some reason, you don't see the body in the dynamic content, try directly entering the text under the expression but using the name of the select action you specified in your Power Automate. In my case it's 'Select_Skill' where _ is added for the space.
The above formula just performs an intersection and we use the same array as parameters, so it gets the unique values. If you just want the unique skills in an array you could capture this into an array variable.We finally use length to get the count of the unique values.
Here is the output for the above action which sets the distinct count
The above process explains one way to do this and there might even be better ways to accomplish this. If you know a better approach please let me know in the comments.
While this is not ideal, please check back the functions reference to see if a function is available when you need to implement this. Power Automate is rapidly evolving and hopefully more functions are available to use in the expression editor.