Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"?
Solved! Go to Solution.
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.
Proud to be a Super User!
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
Thanks,
Lydia Zhang
@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.
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)
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |