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
Em1993
Frequent Visitor

How to see if partial text in one field matches text in another field

I have two code fields that are in two different tables. I need to see if the code in one field matches fully or paritally to the code in the other field. I then need to return the value from a different ID field if it matches. Any ideas how to do that? I've been stuck on it for the past few days. 

Below you can see the code on the left is almost matching to the right except for the -JUL22- portion. Is there I way I can match them based off if part of the code (such as the first half) matches the other?

Em1993_0-1668781396484.png

 

 

Thanks!

7 REPLIES 7
ronrsnfld
Super User
Super User

Depending on your data, a Table.FuzzyJoin or Table.FuzzyNestedJoin might do the trick.

Fuzzy wont work because I need exact matches. I would have a large amount of data I would have to go through using a fuzzy match which I'm trying to avoid

Now that you've presented some sample data:

If your data sample is truly representative, I suggest you add a custom column to the order table that extracts the account code.

eg:  In the AddColumn dialog box:

 

 

=Text.Combine(List.RemoveRange(Text.Split([Order Code],"-"),2),"-")

 

 

ronrsnfld_0-1668782563098.png

 

 

Then use that for the key in a Table.Join or Table.NestedJoin

   

Sometimes it wont have that 17583 as well though. I think what needs to happen is check and see if the order code string is present in the account string, if so I need to return a new column

Use the same principle.

If you want an exact match of certain segments, you will need to figure out which segments you require in order to obtain that exact match.  Then just create a new column for each table that has those determinant segments, and use that as the key.

PhilipTreacy
Super User
Super User

Hi @Em1993 

 

You could maybe use Text.Contains or List.Contains but hard to give any useful answer without seeing your data.  Please provide your data, or a subset that is repesentative of the whole, and show the end result you want.

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Just included an example in my original post

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.

Top Solution Authors
Top Kudoed Authors