Issues in "Remove Duplicates"

[Thulasiraman]

Hi all,

I have 4 different master files which contains duplicate values (Names).

I appended all the 4 files in power query and applied trim function and removed duplicates.

But still when the file is loaded it shows duplicate values (names).  I found out that there

are some spaces in between the words in similar names.

Example:   

Nelson and company

Nelson  and company (contains 2 spaces in between Nelson and)

How to remove duplicates in such cases.  Pls suggest and help.

[v-stephen-msft · Accepted solution]

Hi @Thulasiraman ,

Here's a function from this link.

(text as text, optional char_to_trim as text) =>
let
char = if char_to_trim = null then " " else char_to_trim,
split = Text.Split(text, char),
removeblanks = List.Select(split, each _ <> ""),
result=Text.Combine(removeblanks, char)
in
result

Please go to the power query editor. Create a blank query, then enter the above code in the advanced editor.

Then you can add a custom using this function.

Now you can remove the column with the wrong spaces, keeping your new column.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Thulasiraman]

Hi @v-stephen-msft 

This code works and thanks for your support and detailed explanation.

Ton of thanks.

Thulasiraman

[BA_Pete]

Hi @Thulasiraman ,

Select the appended [Name] column.

Go to the Transform tab > Replace Values.

Find "  " (double space).

Replace with " " (single space).

Then do your remove duplicates step.

Pete

[Thulasiraman]

Hi @BA_Pete 

Thanks for reply.  I tried it but the space is not getting removed!

Thulasiraman

[BA_Pete]

Hi @Thulasiraman ,

Can you share your query here please?

You can copy the whole thing in Advanced Editor and paste it into a code window here:

Just remove any sensitive info from connection strings in the Source and navigation steps.

Pete