Removing Duplicates

[zyniekies]

Hi team, 

I have a situation where I have some data with same Material Number but different Material Description written in different language. 

I only need one of it, preferably the one written in English. 

I thought it will be good to just use 'Remove Duplicates', but then it will remove both. 

Is there any other ways that I can achieve this?

Appreciate your help and guidance, thank you!

[Nasif_Azam · Accepted solution]

Hey @zyniekies ,

To keep only the English description, you need a slightly different approach. Here are a couple of options I recall:

Option-1: Use a Helper Column with a Formula

1) Add a new column namely may be "Language Check".

2) Use a formula to identify English descriptions. For example:

=IF(ISNUMBER(SEARCH("A-Z",[@MaterialDescription])),"English","Other")

Adjust logic based on your data pattern. If English descriptions contain only Latin letters.

3) Filter the helper column to show only “English” and then remove duplicates based on Material Number.

After that you can easily remove the duplications.

Option-2: Use Power Query

1) Load your data into Power Query.

2) Sort the data so that English descriptions appear first for each Material Number.

3) Use Remove Duplicates in Power Query, keeping the first occurrence (which will be English).

Load the cleaned data back to Excel.

Both approaches are worked fine.

Best Regards,
Nasif Azam

[zyniekies]

Hi @Ritaf1983 , @v-dineshya , @danextian , @Nasif_Azam 

Appreciate the reminder and advise on following the right format to post questions here - definitely will take not on that for next time around.

I resolved this by simply removing duplicates by the Material Number as I only need one row for each Material Number.

Thank you 😊

[v-dineshya]

Hi @zyniekies ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Regards,

Dinesh

[v-dineshya]

Hi @zyniekies ,

We haven’t heard from you on the last response and was just checking back to see, Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Regards,

Dinesh

[danextian · Accepted solution]

It’s straightforward to remove duplicates in the query editor. Simply select Material Number, right-click, and choose Remove Duplicates—this works if you only need one row per material number. If you require more detail, you’ll need to include all other relevant columns except for Material Description.  The real challenge lies in detecting the language of a free-text field, since neither M nor DAX provides a built-in function for language identification that doesnt use some web service API.

[Nasif_Azam · Accepted solution]

Hey @zyniekies ,

To keep only the English description, you need a slightly different approach. Here are a couple of options I recall:

Option-1: Use a Helper Column with a Formula

1) Add a new column namely may be "Language Check".

2) Use a formula to identify English descriptions. For example:

=IF(ISNUMBER(SEARCH("A-Z",[@MaterialDescription])),"English","Other")

Adjust logic based on your data pattern. If English descriptions contain only Latin letters.

3) Filter the helper column to show only “English” and then remove duplicates based on Material Number.

After that you can easily remove the duplications.

Option-2: Use Power Query

1) Load your data into Power Query.

2) Sort the data so that English descriptions appear first for each Material Number.

3) Use Remove Duplicates in Power Query, keeping the first occurrence (which will be English).

Load the cleaned data back to Excel.

Both approaches are worked fine.

Best Regards,
Nasif Azam

[Ritaf1983]

Hi @zyniekies 
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523