PowerQuery Contains

[kcantor]

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?

[blopez11]

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

[kcantor]

@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]

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

[kcantor · Accepted solution]

@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]
				)
			)
		)
	)
)

[v-haibl-msft]

@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

[blopez11]

Awesome, I'll bookmark this for future reference

Thanks for sharing