Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors