Extraction of number (or text) from a column with both text and number – #PowerQuery #PowerBI
Extraction of number (or text) from a column with both text and number – #PowerQuery #PowerBI
When you are working with data in Excel or PowerBI the data often contains columns that is a combination of text and numbers.
One example could be like this
If you have this challenge you shouldn’t use Split Columns or Text.Range to do this but check out
Text.Select
Documentation here
And Chris Webb has good example using it for text – here.
My example demonstrates how to work with text but also works with numbers and capitals letters and symbols etc.
Here is how we can extract the House number and Zip Code – use the Custom Column from the Add Tab in the Query Editor window
= Table.AddColumn(Source, “Housenumber”, each Text.Select([Street], {“0”..”9″}))
= Table.AddColumn(#”Added Custom”, “Zip Code”, each Text.Select([Zip], {“0”..”9″}))
And now we have
And one other benefit is that the Function doesn’t return an error when there is no number in the string.
Here is an example file
Hope you find this useful