cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FabrPrado
Frequent Visitor

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

Un1titled.png

 

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

 

 

 

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

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

2017-10-07_0-03-18.png

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

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
 

View solution in original post

20 REPLIES 20
MKI_GPCG
Regular Visitor

 

Capture.PNG

 

I am using power query in Power BI 

 

@TomMartens 

Hey @MKI_GPCG ,

 

please provide a pbix that allows to recreate the error, upload the pbix to onedrive or dropbox and share the link.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hey @MKI_GPCG ,

 

you have to upload the xlsx as well, as without the datasource it's not possible to do any Power Query.

 

Please consider to add a parameter to the pbix that points to the directory where the xlsx is located and allows faster adjustment to different environments: https://docs.microsoft.com/en-us/power-query/power-query-query-parameters

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable


@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

Un1titled.png

 

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. 

TomMartens
Super User
Super User

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

2017-10-07_0-03-18.png

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

hi Tom,

 

Your code works perfectly. However, how can I revise your code so that there will be a space between 1 and 2 on the third row to show there are other texts between these 2 numbers in the original cell? 

 

 

 

 

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

 

Anonymous
Not applicable

Hello Tom,
Thanks for this code, it worked. but i got a small extension to this.

My data has decimals too, i mean 
6
6.5 Nos
10.4 kgs etc
here i am getting
6
65
104.
Any help to make the decimal retain in the output.
Anyone with any suggestion? TIA.

Hey @Anonymous , maybe you want to try a simple Split by delimiter transformation

image.png

then

image.png

Tada

image.png

 

Hopefully, this is what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Thank you Tom for helping it out.

Hello Tom,

Very helpful code you provided here. However, would it be possible for you to send a DAX version of this? 
I am having difficulties trying to convert it from M to DAX.

Best regards,
Denis

Hey @denle ,

 

to create a calculated column you can use this DAX to extract the numbers from a string:

a Column with only numbers = 
var __string = 'Table'[The text]
var __stringLength = LEN( __string )
var thenumbers = 
    CONCATENATEX(
        FILTER(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    GENERATESERIES( 1 , __stringLength , 1 )
                    , "@char" , MID( __string , ''[Value] , 1 )
                )
                , "isnumber" , IF( ISERROR( VALUE( [@char] ) ) , 0 , 1 )
            )
            , [isnumber] = 1
        )
    , [@char]
    )
return
thenumbers

 Here is a little screenshot:

TomMartens_0-1606331188729.png

 

Hopefully, this provides what you are looking for. Nevertheless, I recommend to extract the numbers using Power Query instead of DAX.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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
 

Hey @MKI_GPCG ,

 

you have to be aware that my solution can only be used inside Power Query as is using M functions. I mention this because you are referencing DAX.

 

Please describe in much more detail where you are trying to use my formula please provide screenshots

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Test = Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Advocacy Time During Appointment]),each if Value.Is(Value.FromText(_), type number) then _ else null)))
 
guys im getting an 

Your formula works perfectly @TomMartens !

 

Thanks for sharing 😉

great

it works perfectly ! 

thanks a lot

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 ?

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"})

 

Very good, working;

Thank TomMartens

 

See how my table is:

Capture.PNG

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors