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
arthastic
Advocate I
Advocate I

How to remove nonessential characters such as punctuation, numbers, web addresses

Hi!

Help me please with removing nonessential characters from text column.

I use Replace Values function to remove this characters:

= Table.ReplaceValue(#"Removed Columns1",",","",Replacer.ReplaceText,{"message"})

How to modify this formula to replace several characters in one step. Or is it ony other way to remove special symbols.

 

Regards!

1 ACCEPTED SOLUTION

@Greg_Deckler: Text.Trim only removes characters at the start or at the end of a text.

 

My suggestion would be to convert the text to a list of characters, remove the unwanted characters and return the result back to a text.

 

= Text.Combine(List.RemoveItems(Text.ToList("M,,,a.r;;;celB.;e.u.;g"),Text.ToList(",.;")))

 

Specializing in Power Query Formula Language (M)

View solution in original post

8 REPLIES 8
arthastic
Advocate I
Advocate I

Thank you guys for your help!

 

Regards!

Sean
Community Champion
Community Champion

@arthastic

You can also use the User Interface to

Trim - Remove leading and trailing whitespaces from each cell in the selected columns!

Clean - Remove non-printable characters in the selected columns!

 

QE- Trim and Clean.png

 

MarcelBeug
Community Champion
Community Champion

@Sean I'm afraid you are mixing up "non-essential" with "non-printable".

This solution will not return "MarcelBeug" from my example. Smiley LOL

Specializing in Power Query Formula Language (M)
Sean
Community Champion
Community Champion

@MarcelBeug

Transform tab - Forrmat button - Clean => read the Tooltip! Smiley Happy

 

QE- Trim and Clean with Tooltip.png

MarcelBeug
Community Champion
Community Champion

@Sean

Turn audio on => watch the Video!

Specializing in Power Query Formula Language (M)
Sean
Community Champion
Community Champion

@MarcelBeugSmiley Very Happy Smiley LOL

I never said Clean would be a solution!

Before @arthastic accepted your answer as a solution he gave smpoure the heads up as if Trim would be enough!

So I started preparing my answer before yours was marked as the solution!

Just wanted to show the Trim can be accessed through the User Interface

And because Clean is right underneath Trim I decided to include it - thats it - nothing else! Smiley LOL Smiley LOL Smiley LOL

 

EDIT: The Audio is amazing! Smiley LOL

Greg_Deckler
Super User
Super User

Use Text.Trim and specify the optional trimChars parameter:

https://msdn.microsoft.com/en-us/library/mt260494.aspx

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler: Text.Trim only removes characters at the start or at the end of a text.

 

My suggestion would be to convert the text to a list of characters, remove the unwanted characters and return the result back to a text.

 

= Text.Combine(List.RemoveItems(Text.ToList("M,,,a.r;;;celB.;e.u.;g"),Text.ToList(",.;")))

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.