Remove duplicates - Comparison remove data from array

Like (0) Share

Report

Posted on 17 Jun 2024 15:51:50 by PA-Noob

Hello everyone,

I spent couple of days trying to get Power Automate to remove duplicates from a Sharepoint List to automate emails.

My list is organised as followed: Title, Status, Full Name, Email Address, ID

The type of duplicate I will have will be as such:

Title, Status, Full Name, Email Address, ID

001, To send, Name 1, Email 1, 1

001, To send, Name 1, Email 1, 2

My goal is to email all the people in my list only once and ignore the duplicate which will be deleted later in the flow.

My current flow aims to get all items and store all their information in a variable "All contacts" (array).

Then, I have a second variable "Unique contacts" (array) that is empty and I want to use in a condition action.

The condition action checks if the Title in "All contact" is NOT in "Unique contact". If true, it adds all the information to Unique contacts, and go to next.

Screenshot 2024-06-17 144812.png

(The compose actions are here to see the output and troubleshoot. Power Automate sometimes do not show the input output without a refresh which is time consuming...)

In all the array variables the information is formatted as such:

{
"Tile": "@{item()?['Title']}",
"Email": "@{item()?['Email']}",
"ID": "@{item()?['ID']}",
}

I am not used to work with array and struggle to get PA to compare the Title of the objects in both arrays. I tried:

variables('Unique contacts') DOES NOT CONTAIN items('Compare arrays')?['Title']

But is give TRUE to all entries (incl the duplicates). I figured it is because I do not point to the Unique contacts Title and the current items Title in the loop. However code below gives me the error:

variables('Unique contacts')?['Title'] DOES NOT CONTAIN items('Compare arrays')?['Title']

Action 'Compare arrays' failed: Unable to process template language expressions for action 'Compare arrays' at line '0' and column '0': 'The template language expression 'variables('Unique contacts')?['Title']' cannot be evaluated because property 'Title' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

I thought it was because "Unique contacts" is empty and does not have a Title to select on the first iteration of the loop, so I set the initial value:

[
 {
 "UKSCB Ref": "Empty"
 }
]

But this gave me the same error.

I have tried everything I think could work and I am now clueless about what the issues is (comparison statement? empty array? something else?).

May I ask for your advice?

I greatly appreciate any contributions to this problem.

All responses (10)

Answers (1)

v-mengmli-msft on 21 Jun 2024 at 09:17:52

Like (0)

Report

Re: Remove duplicates - Comparison remove data from array

Hi @PA-Noob ,

If my answer helps you solve the problem, please accept it as a solution as well.

Thanks.

Rimmon
Verified answer

PA-Noob 41 on 20 Jun 2024 at 10:38:00

Like (0)

Report
Re: Remove duplicates - Comparison remove data from array

In case people experience the same problem, I found the solution to the comparison.
The condition action of Power Automate cannot compare the values associated to keys of an array.
You must convert your array in a string using a custom formula and the "join" operator:
join(variables('Unique contacts'), ',')

Credit to ChatGPT!
PA-Noob 41 on 20 Jun 2024 at 10:34:02

Like (0)

Report

Re: Remove duplicates - Comparison remove data from array

Many thanks for your contribution Rimmon.
Unfortunately, without the capacity to handle all the parameters of my array, your solution cannot fulfil my goal.
v-mengmli-msft on 19 Jun 2024 at 07:53:04

Like (1)

Report
Re: Remove duplicates - Comparison remove data from array

Hi @PA-Noob ,

Have you tried my method?

My flow is doing something like what you said. Remove duplicates using Title.

You can modify "Append to array variable" to keep what you need, such as ID and email.

The expression for save distinct email to array.

first(body('Filter_array'))?['EmailColumn']

After the last loop, the array contains all distinct Title and Email.

Then, add Send an email action in Apply to each and use following expression in 'To' field.

item()?['EmailColumnName']

Each user will just receive one email.

Best regards,

Rimmon
PA-Noob 41 on 18 Jun 2024 at 09:54:06

Like (0)

Report

Re: Remove duplicates - Comparison remove data from array

Hi Rimmon,

The process aims to remove duplicates using Title, then use the email address to send the email, and use the ID to delete duplicates. As a result, I must keep the Title associated with the email and ID for the flow to fill its goal 😊.

Best regards,
v-mengmli-msft on 18 Jun 2024 at 09:36:07

Like (0)

Report

Re: Remove duplicates - Comparison remove data from array

Hi @PA-Noob ,

The ID are auto populated by SP list, they are distinct. I don't recommend you use ID to remove duplicate records.

As soon as you have got distinct Title, then you can use it in Apply to each to filter items with same Title.

The filter results are records with same information excepts ID. You just need to use first in expression to get one record and append to array variable.

——————————————————————————————————————————————————

If my answer helps you solve the problem, please accept it as a solution.

If you have another question, please post it in new post.😀

Best regards,

Rimmon
PA-Noob 41 on 18 Jun 2024 at 09:10:22

Like (0)

Report

Re: Remove duplicates - Comparison remove data from array

Hi Rimmon,

Thank you for the clarification.
Upon testing with the Title only I does work! I do not get why the union of a list with itself removes duplicates 🤔

However, as soon as I pass the other information (Title, Email Address, ID) it ceased to function the duplicates are back in the output. Would that be because of the IDs will be different for every entries?
v-mengmli-msft on 18 Jun 2024 at 07:30:26

Like (0)

Report
Re: Remove duplicates - Comparison remove data from array

Hi @PA-Noob ,

There is only one list in my test.

Do you notice that I used Select to get all Title value?

When you put two same arrays or collections in union, one array with no duplicates will be returned.

For example.

union([1,2,2,3],[1,2,2,3])

The result is [1,2,3]

So, the result in first Compose is all unique Title.

Then we can use each Title in Compose to get items with same Title and just append one of items to array variable.

The array variable contains all unique items.

Best regards,

Rimmon
PA-Noob 41 on 18 Jun 2024 at 07:16:42

Like (0)

Report

Re: Remove duplicates - Comparison remove data from array

Hi Rimmon,

Thank you for replying and helping me on this challenge.

From my understanding, union will be a great tool to compare two sharepoint lists and isolate the duplicates.
Unfortunately, I only have a single sharepoint list containing duplicated entries. Without doing some data operation to create a second data set to compare it to the original, I do not think union will be able to discriminate the duplicates?!

That is why I opted for two variables approach. One to store all the entries, and then progressively copy any new Title in the second variable to end up with a list of unique entries.

Best regards,
v-mengmli-msft on 18 Jun 2024 at 01:40:44

Like (1)

Report

Re: Remove duplicates - Comparison remove data from array

Hi @PA-Noob ,

Do you want to get unique contact from SP list?

You can try union in expression. Reference guide for expression functions - Azure Logic Apps | Microsoft Learn

First, we can use Select action to get all Title.

Then, we can use union(<All title>,<All title>) to get unique Title.

At last, we use unique Title to find items with same Title and use first to just get one item.

Here is the guidance for your reference.

My List

The result in Compose2

Best regards,

Rimmon