Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kcantor
Community Champion
Community Champion

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION
kcantor
Community Champion
Community Champion

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
blopez11
Resident Rockstar
Resident Rockstar

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

kcantor
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Community Champion
Community Champion

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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

Awesome, I'll bookmark this for future reference

Thanks for sharing

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.