Keep only numbers in a string / remove all non-numbers

user_1867581 0
Viewed 6803

I have a string column where I only need to the numbers from each string, e.g.

A-123  -> 123
456    -> 456
7-X89  -> 789

How can this be done in PowerQuery?

m powerbi powerquery
3 Answers

Add column. In custom column formula type this one-liner:

= Text.Select( [Column], {"0".."9"} )

where [Column] is a string column with a mix of digits and other characters. It extracts numbers only. The new column is still a text column, so you have to change the type.

Edit. If there are dots and minus characters:

= Text.Select( [Column1], {"0".."9", "-", "."} ))

Alternatively, you can transform the existing column:

= Table.TransformColumns( #"PreviousStepName" , {{"Column", each Text.Select( _ , {"0".."9","-","."} ) }} )
user_19037930
Related