cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User III
Super User III

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

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

Highlighted
Regular Visitor

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

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

14 REPLIES 14
Highlighted
Super User III
Super User III

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

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

Highlighted
Frequent Visitor

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

 

Very good, working;

Thank TomMartens

 

See how my table is:

Capture.PNG

Highlighted
Helper III
Helper III

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

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 ?
Highlighted
Regular Visitor

Re: NEED HELP FOR EXTRACT ONLY 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"})

Highlighted
Advocate I
Advocate I

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

it works perfectly ! 

thanks a lot

Highlighted
Skilled Sharer
Skilled Sharer

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

Your formula works perfectly @TomMartens !

 

Thanks for sharing 😉

great

Highlighted
Anonymous
Not applicable

Re: NEED HELP FOR EXTRACT ONLY NUMBERS


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

Highlighted
Regular Visitor

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

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 
Highlighted
Regular Visitor

Re: NEED HELP FOR EXTRACT ONLY NUMBERS

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors