cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Marcus_E Frequent Visitor
Frequent Visitor

Query column CONTAIN

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 fieldDesired result
No purchase order0 or "Blank"
No purchase order0 or "Blank"
No purchase order0 or "Blank"
No purchase order0 or "Blank"
45000123456 Vendor purchase order details45000123456
45000123457 Vendor purchase order details45000123457
45000123458 Vendor purchase order details45000123458
45000123459 Vendor purchase order details45000123459
47000112233 Vendor purchase order details47000112233
47000112234 Vendor purchase order details47000112234
47000112235 Vendor purchase order details47000112235

 

 

Many thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Query column CONTAIN

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

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Query column CONTAIN

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

View solution in original post

Marcus_E Frequent Visitor
Frequent Visitor

Re: Query column CONTAIN

@d_gosbell  works perfectly, thanks for your response.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,569)