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

Extract numbers from text

What's the most efficient way to extract numbers from a column that has a combination of both numbers and text? E.g.

 

1

1234

"hello World"

0

"arbitrary text"

should return

 

1

1234

null

0

null

 

Is there an M / PowerQuery formula for this scenario equaivalent to the t-sql "try_cast" or "try_convert"?

1 ACCEPTED SOLUTION
KHorseman
Community Champion
Community Champion

In DAX you can use ISNUMBER.

OK I should have tested that first. You were actually on the right track anyway. ISERROR(VALUE(TableName[ColumnName])) will return true if the value isn't a number, false if it is a number.

 

In Power Query it's a two-step formula but you can nest them: Value.Is(Value.FromText([ColumnName]), Int64.Type) will return true if the row contains a number value, false if not.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
v-yuezhe-msft
Microsoft
Microsoft

Hi @sethsanu,

Adding to other’s post, to output the column to the your desired format, just create a new calculated column using this formula: Column 2 = IF(ISERROR(VALUE(Table1[Column1])),"null",Table1[Column1]), for more details, please check the following screenshot

1.PNG




Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft that would come out to be a text column wouldn't it? I think @sethsanu meant he wanted a null result for text rows, rather than the actual text "null" but I could be mistaken.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Correct - I need to do the exclusion prior to importing at the M level. Thanks KHoreman - the M query test works fine! (marked as solution)

KHorseman
Community Champion
Community Champion

In DAX you can use ISNUMBER.

OK I should have tested that first. You were actually on the right track anyway. ISERROR(VALUE(TableName[ColumnName])) will return true if the value isn't a number, false if it is a number.

 

In Power Query it's a two-step formula but you can nest them: Value.Is(Value.FromText([ColumnName]), Int64.Type) will return true if the row contains a number value, false if not.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.