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 II
Super User II

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 II
Super User II

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

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

 

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 @aravindsampath , 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

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
 

View solution in original post

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors