cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cfoenki
Frequent Visitor

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

Hello to the forum!

 

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

I have a collection:

Person_emailRole_TypeCountry_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_emailRoleCountry_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 🙂

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-albai-msft
Microsoft
Microsoft

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));

 

v-albai-msft_0-1617790092069.png

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)}))

 

v-albai-msft_1-1617790275749.png

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"))

121.gif

Best regards,

Allen

 

View solution in original post

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 😄

View solution in original post

3 REPLIES 3
v-albai-msft
Microsoft
Microsoft

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));

 

v-albai-msft_0-1617790092069.png

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)}))

 

v-albai-msft_1-1617790275749.png

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"))

121.gif

Best regards,

Allen

 

View solution in original post

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.

 

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 😄

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

Top Solution Authors
Top Kudoed Authors
Users online (45,123)