Query column CONTAIN

[Marcus_E]

Hi

I'm fairly new to Power BI and am hoping some more experienced users could help point me in the right direction.

I have a data set of transactions from a procurement system that includes a column originating from a 'free-form' entry field into which users can manually enter a purchase order number. Not all records have a purchase order number and the data can include both numeric purchase order details as well as random text information.

I'm trying to modify my query to extract from any records that contain a purchase order number, the actual purchase order number and return it to a new column in the query. If I was using Excel I would use a CONTAIN filter but as I don't want to filter I need some way to extract the 11 digit number and return it to a separate column which retaining all other records that don't have a purchase order number in this column.

For example the numbering sequence for all purchase orders begins with either '4500' or '4700' and are 10 characters long, so I'd like to a) recognise each record that contains '4500' or '4700' and extract the first 10 characters. Any records that don't match this criteria would simply remain blank.

An example of the data set and desired outcome is tabled below.

Existing data field	Desired result
No purchase order	0 or "Blank"
No purchase order	0 or "Blank"
No purchase order	0 or "Blank"
No purchase order	0 or "Blank"
45000123456 Vendor purchase order details	45000123456
45000123457 Vendor purchase order details	45000123457
45000123458 Vendor purchase order details	45000123458
45000123459 Vendor purchase order details	45000123459
47000112233 Vendor purchase order details	47000112233
47000112234 Vendor purchase order details	47000112234
47000112235 Vendor purchase order details	47000112235

Many thanks.

[d_gosbell · Accepted solution]

So in your query under "Add Column" you could click the "Add Custom Column" button and then paste in logic like the following:

if Text.StartsWith([Existing data field],"4500") 
or Text.StartsWith([Existing data field],"4700") 
then Text.Start([Existing data field],10) 
else null

[Marcus_E]

@d_gosbell  works perfectly, thanks for your response.