cancel
Showing results for 
Search instead for 
Did you mean: 
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
Memorable Member
Memorable Member

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors