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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 


@ 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!:
Mastering Power BI 2nd Edition

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.