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.