Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.