cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

PowerQuery Contains

I have two tables that have a part number field. Is there a way in Power Query to see if Table1[VendorItem] contains the text found in Table2[Part#] ?

They would not be exact matches so a merge is out of the question.

For example, the vendor item might be T49 and the [Part#] might be T49WHRH to specify color options amoung other things.

Any ideas on making this happen?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: PowerQuery Contains

@KHorseman send me a wonderful little snippet of Dax for a table. It was a thing of beauty and worked beautifully.

new table = GENERATE(
	Table1,
	FILTER(
		Table2,
		NOT(
			ISERROR(
				FIND(
					Table2[PART#],
					Table1[Vendor Item]
				)
			)
		)
	)
)
6 REPLIES 6
blopez11 Established Member
Established Member

Re: PowerQuery Contains

Trying to understand what your overall goal is?  Maybe able to offer options if we understand what you are trying to achieve

Super User
Super User

Re: PowerQuery Contains

@blopez11

If 'Table1[ItemNumber] contains an approximate match to the text in found in 'Table2[Part#], then return the 'Table2'[Part#]  into a new column in Table 1 else leave it blank.

I would be happy to do this either in query editor or in a calculated column.

blopez11 Established Member
Established Member

Re: PowerQuery Contains

I think you would want to go with a calculated column

I'm not sure if there would be multiple matches in your data set during the lookup but you can take a look at the following as it may apply to your case

https://www.powerpivotpro.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-i...

Hope this helps

Super User
Super User

Re: PowerQuery Contains

@KHorseman send me a wonderful little snippet of Dax for a table. It was a thing of beauty and worked beautifully.

new table = GENERATE(
	Table1,
	FILTER(
		Table2,
		NOT(
			ISERROR(
				FIND(
					Table2[PART#],
					Table1[Vendor Item]
				)
			)
		)
	)
)
blopez11 Established Member
Established Member

Re: PowerQuery Contains

Awesome, I'll bookmark this for future reference

Thanks for sharing

 

v-haibl-msft Super Contributor
Super Contributor

Re: PowerQuery Contains

@kcantor

 

An excellent solution. You could mark your answer as solution to close this thread and help others to know the right answer.

 

Best Regards,

Herbert