Forum Discussion

chmoon's avatar
chmoon
Frequent Visitor
9 years ago
Solved

Removing Numbers

I was wondering if anyone knew of a more elgant way to remove numbers from a string. I realized that Power BI does not support regular expressions. I know I could use R or another method to remove, b...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    In the Query Editor you can either add a column with formula:

     

    = Text.Combine(List.RemoveItems(Text.ToList([TextWithDigits]),{"0".."9"}))

    Or transform the column: first choose Transform - Clean (or some other option) and then adjust the code in the formula bar to:

     

     

    = Table.TransformColumns(PreviousStep,{{"TextWithDigits", each Text.Combine(List.RemoveItems(Text.ToList(_),{"0".."9"}))}})

    the adjustment is the part starting with "each".

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

You can use R to run a regular expression over your column if that helps.

 

If you give me your preferred Regex pattern I can try and get it going for you.

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    The R column in Power Query might look like this :

     

    Just replace Column1 with your own column.  This will append on a new column stripped of numbers.

     

    This allows you to extend your regular expression to handle other patterns, so could be quite versatile

     

    pattern<-"[A-Z]+"
output <-within(dataset,{RegExCol=sapply(sub('[0-9]+', '', dataset$Column1), function(x) toString(x)) })