cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Marcus_E
Helper I
Helper I

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
d_gosbell
Super User II
Super User II

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
d_gosbell
Super User II
Super User II

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

@d_gosbell  works perfectly, thanks for your response.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors