Forum Discussion
Solved
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 ...
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.
Anonymous 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.
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)