cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thulasiraman
Helper II
Helper II

Issues 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.

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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.

vstephenmsft_1-1658739794665.png

vstephenmsft_2-1658739890640.png

 

Then you can add a custom using this function.

vstephenmsft_3-1658739922930.png

vstephenmsft_4-1658739929164.png

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.

 

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

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.

vstephenmsft_1-1658739794665.png

vstephenmsft_2-1658739890640.png

 

Then you can add a custom using this function.

vstephenmsft_3-1658739922930.png

vstephenmsft_4-1658739929164.png

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.

 

Hi @v-stephen-msft 

 

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

Ton of thanks.

 

Thulasiraman

BA_Pete
Super User
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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi @BA_Pete 

 

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

Thulasiraman

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:

BA_Pete_0-1658387712421.png

 

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

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors