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".

     

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

Thanks for kudos, guys, but - shame on me :smileyembarrassed: - today I was triggered by a question from MattAllington on MrExcel.com involving Text.Remove, which can be used instead of my creative formula, which can be simply replaced by:

 

Text.Remove([TextWithDigits],{"0".."9"})

 

 

 

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    MarcelBeug  Thanks for the Update!

     

    I will actually use this in place of Step 2 here

    http://community.powerbi.com/t5/Desktop/split-multiple-column-values-into-multiple-rows/m-p/144081#M62155

    This Step 2 seems to remove everything after a number - so if a cell starts with a number it will be left empty

    = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.Start(_, Text.PositionOfAny(_,Text.ToList("0123456789"))), type text}})

     

  • rtse's avatar
    rtse
    Frequent Visitor
    8 years ago

    Hi, I'm new to PowerBI and SQL.  I have used your recommended formula to remove numbers from a text string but PowerBI is giving me an error.  What am I doing wrong ?  thanks

     

     

    • rtse's avatar
      rtse
      Frequent Visitor
      8 years ago

      I reinput the command and it worked !!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Just what I wanted! Thanks!