Power Query: Select a Number

Welcome to our Power Query blog. This week, I look at extracting a number from a mixed column.

Data can come with a mixture of text and numbers in the same column. I will look at a couple of simple ways to extract the part that I want. A while back, in Power Query: Candid Columns, I converted some data (which John the imaginary salesperson had provided) from a column to a table:

This time, I want to break down the address further, and pull out the building number from the rest of the column. I could try splitting the column, but the building number has a variable number of digits. There are a few ways I could approach this, but I will specifically look at two of them. I start by creating a query ‘From Table’ from the ‘Get & Transform’ section on the ‘Data’ tab:

Text.BeforeDelimiter() 

This is described by Microsoft as:

Text.BeforeDelimiter(text as nullable text, delimiter as text, optional index as any) as any 

This returns the portion of text before the specified delimiter. An optional index indicates which occurrence of the delimiter should be considered. 

This is the method Power Query uses if I add a column using ‘Column by Examples’:

The M code used is  

This code takes the portion of the Address column from the beginning until the first space (“ “).  Whilst this gives me the building number, it is not so easy to apply to the rest of the address.  There is another function that I can use which is more flexible.

Text.Select() 

This is described by Microsoft as:

Text.Select(text as nullable text, selectChars as any) as nullable text 

This returns a copy of the text with all the characters not in  selectChars  removed. 

I can choose which characters I want to keep.  I want to keep all numbers so as to retain the building number.

I have added a custom column using the following M code: 

This tells Power Query to select all of column Address which matches anything in the list of numbers.  However, since I am picking ALL of the numbers, I could also write it this way 

For more on list syntax, please check out Power Query: Birthday Lists.

I can also use this method to extract the rest of the address:

The M code I have used this time is; 

This selects characters in Address which are lower case letters, upper case letters or a space, and gives me the street name.

Come back next time for more ways to use Power Query!