Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Thanks!
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),"-")
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.
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
Proud to be a Super User!
Just included an example in my original post