Power Apps

Answered

Delete duplicates in a collection - Keep only distinct items with all columns value

(0) Share

Report

Posted on by cfoenki

100

Hello to the forum!

I am fighting with powerapps and trying many solutions from forums with no success...

I have a collection:

Person_email

Role_Type

Country_Name

first@email.com

Value

Quality

Belgium

second@email.com

Value

Quality

France

first@email.com

Value

Utilities

Regulatory

Belgium

third@email.com

Value

Regulatory

Spain

What I would like to do is to delete duplicates (here first@email.com) but keep the both Role values (here keep Quality, Utilities and Regulatory).

Taget collection:

Person_email

Role

Country_Name

first@email.com

Value

Quality

Utilities

Regulatory

Belgium

second@email.com

Value

Quality

France

third@email.com

Value

Regulatory

Spain

It sounds simple explain like this but I trying so hard with no success that I imagine it is not that easy...

Thank you to all members who could help me 🙂

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (2)

Sort by

Verified answer

v-albai-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @cfoenki ,
Seems that Role_Type column is a nested table.
I did a test on my side, check if my formula is useful to you:
1. Remove duplicates based on Person_email column and keep the result into a collection a1:

ClearCollect(a1,Distinct(collectionname,Person_email));

2. Based on a1, create a new collection a2, put data in Role_Type together if Person_email is duplicate:

ForAll(a1,Collect(a2, {Person_email:Result, Role1:Left( Concat( Filter(list9,Person_email=Result).Role_Type, Role_Type& ", " ), Len( Concat( Filter(list9,Person_email=Result).Role_Type, Role_Type& ", " )) - 2 ),Country_Name:LookUp(list9,Person_email=Result,Country_Name)}))

3. Splits text in Role1 column of collection a2 into a table:
ClearCollect(a3,ShowColumns(AddColumns(a2,"Role",RenameColumns(Split(Role1,","),"Result","Value")),"Person_email","Role","Country_Name"))
Best regards,
Allen

Was this reply helpful? Yes No
cfoenki 100 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you so much for your reply.
I tested it but I have an error on the second formula:

ForAll(a1,Collect(a2,
{Person_email:Result,
Role1:Left(Concat(Filter(Sharepoint_Assignation,Person_email=Result).'Role type', 'Role type' & "," ), Len(Concat(Filter(Sharepoint_Assignation,Person_email=Result).'Role type'; 'Role type' & ", " )) - 2 ),Subsidiary_Name:LookUp(Sharepoint_Assignation,Person_email=Result,'Subsidiary Name'.Value)}))

I tested with Replacement of red text by 'Role type'.Value and Value but it still gets an error.

Role Type is collected from a combobox in a Sharepoint list. I don't know if it changes something.

Was this reply helpful? Yes No
Verified answer

cfoenki 100 on at

Like
a
(0)

Report
Copy link

Link copied!
I found a way to do it by modifying your example.

First I collect my initial collection (Temp_Assignation) containing duplicates but I changed the title of the nested Role_Type table by "Role".

Then as you mentionned I collected a temporary collection of distinct emails:
ClearCollect(a1,Distinct(Temp_Assignation,Person_email));

Then I Clear and collect items from the Temp_Assignation list using the a1 list:
Clear(a2); ForAll(a1,Collect(a2, {Person_email:Result, Role1:Concat(Filter(Temp_Assignation,Person_email=Result).Role_Type,Concat(Role_Type,Role,";"),";")}));

Then I get similar list as your a2 list and I made a new collection as you've done (mine is named Sharepoint_Assignation):

ClearCollect(Sharepoint_Assignation,ShowColumns(AddColumns(a2,"Role",RenameColumns(Split(Role1,";"),"Result","Value")),"Person_email","Role"));

Thank again for all your help on the subject. Without your reply I think I will still be searching 😄

Was this reply helpful? Yes No