Forum Discussion
Helper IIIssues in "Remove Duplicates"
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.
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 resultPlease 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.
5 Replies
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 resultPlease 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.
ThulasiramanHi Anonymous
This code works and thanks for your support and detailed explanation.
Ton of thanks.
Thulasiraman
- BA_Pete
Super User
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
- 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
