cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kressb
Helper V
Helper V

Remove Characters from Text Column in PowerQuery

Hello, 

I have a column like this:

Person11.00 - Blue; 0.50 - Red
Person21.00 - Blue; 0.10 - Green
Person31.00 - Green; 0.25 - Red
Person40.50 - Red; 0.30 - Green

 

I want to do a transformation in PowerQuery that removes the Decimal Number and the Dash:

Person1Blue; Red
Person2Blue; Green
Person3Green; Red
Person4Red; Green

 

I tried this in Advanced Editor:

GetRidOfNumber =
   Table.TransformColumns(
            #"Changed Type1",
                     {
                     {
                          "Column2",
                             (text)=> Text.Remove(text,{"0".."9"})
                     }
                     }
                                           )
in
GetRidOfNumber

 

That gave me:

Person1. - Blue; . - Red
Person2. - Blue; . - Green
Person3. - Green; . - Red
Person4. - Red; . - Green

How do I get rid of the decimal point and hyphen?

TYIA

1 ACCEPTED SOLUTION
Jakinta
Solution Sage
Solution Sage

You can just expand list for 2nd argument, like this: {"0".."9",".","-"} or go with each Text.Select(_,{"a".."z","A".."Z"," "})...

View solution in original post

1 REPLY 1
Jakinta
Solution Sage
Solution Sage

You can just expand list for 2nd argument, like this: {"0".."9",".","-"} or go with each Text.Select(_,{"a".."z","A".."Z"," "})...

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors