Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |