Forum Discussion

FabrPrado's avatar
FabrPrado
Frequent Visitor
8 years ago
Solved

NEED HELP FOR EXTRACT ONLY NUMBERS

Hi, Good day for All

 

I need more one help:

 

How to extract only numbers from a column

 

|column that I have          | column that I need

 

There is no default, numeric characters can be anywhere in the text

 

 

 

Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    in the Query Editor  add a custom column and use this formula

    Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)))

    replace Column1 with the name of your column.

     

    The idea, put the text of the source column to a list of characters, check each character if it is of type number, if yes keep it, else skip the character.

     

    It's maybe not that obvious, but it's the pattern how regex would do it.

     

    A little picture of my sample

     

    Regards

    Tom

  • MKI_GPCG's avatar
    MKI_GPCG
    6 years ago
    Test = Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Advocacy Time During Appointment]),each if Value.Is(Value.FromText(_), type number) then _ else null)))
     
    getting an error guys- im i missing something? very new to power BI and DAX
     

20 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    Hey,

     

    in the Query Editor  add a custom column and use this formula

    Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)))

    replace Column1 with the name of your column.

     

    The idea, put the text of the source column to a list of characters, check each character if it is of type number, if yes keep it, else skip the character.

     

    It's maybe not that obvious, but it's the pattern how regex would do it.

     

    A little picture of my sample

     

    Regards

    Tom

    • FabrPrado's avatar
      FabrPrado
      Frequent Visitor
      8 years ago

       

      Very good, working;

      Thank TomMartens

       

      See how my table is:

    • Giavo's avatar
      Giavo
      Icon for Helper III rankHelper III
      7 years ago
      Tom, this is a great solution, I have tried it and it worked but in my case I have a small problem: the numbers contained in my text column are DECIMAL number and your solution extracts the numbers and deletes the "." and gives an integer as a result (so 1.0.2.1 is returned as 1021). How would it be possible to keep the original format of numbers ?
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Giavo, try this

         

        Text.Select([SourceColumn], {"0".."9","."})

         

        And a simple way to extract only numbers from a column, would be Text.Select([SourceColumn], {"0".."9"})

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    FabrPrado wrote:

    Hi, Good day for All

     

    I need more one help:

     

    How to extract only numbers from a column

     

    |column that I have          | column that I need

     

    There is no default, numeric characters can be anywhere in the text

     

     

     

    This one does not tackle the decimal point. On the other hand the other solution does.