How to extract continuous string set from a column with multiple criteria

Greg_DecklerI have attached the current list of client names for your reference. You could use this list in your working file if it is convenient.

Drive Link 

Anonymous - OK, I guess I was confused because you have the PBIX and can make those changes on your own?

The only change that I could not make was to have Barclays (ABC) match BARCLAYS (LLP) because those two things do not match. It would have to match on Barclays<=>BARCLAYS which it does and returns Barclays. There is no fuzzy matching with FIND. It is only case-insensitive.

Not the formula. It is returning in the column whatever search word (Project) matches the current row. It is not doing anything with regard to manipulating the text in Client Name, it simply returns the matching Ongoing Project value. So, you can put whatever you want in Table24 as far as extra characters, etc. it doesn't matter one tiny bit, as long as a match is found in Table (24a) then that match is returned as the value. I made no changes to the code, just the changes to the data tables, which you have the PBIX, you can do that.

Anonymous - OK, so after all my complaining about how impossible this was in DAX, I couldn't let it go because I am just a glutton for punishment when it comes to these things. So, here is the first ever (that I am aware of) fuzzy text matching formula in DAX. It's fairly rubbish but it does prove that the concept is actually possible. I have some thoughts for improving its accuracy but my brain is kind of fried after this. Updated PBIX is attached. As you can see, it is not 100% accurate and does some weird things but that's just sort of what DAX does (long explanation over the whole casing of letters).

Column 3 = 
    VAR __ClientTable = ADDCOLUMNS(GENERATESERIES(1,LEN([Client Name]),1),"Letter",MID([Client Name],[Value],1))
    VAR __WordSearchTable = 
        GENERATE(
            DISTINCT('Table (24b)'[Index]),
                VAR __Word = MAXX(FILTER('Table (24b)',[Index]=EARLIER([Index])),[Ongoing Projects])
            RETURN ADDCOLUMNS(GENERATESERIES(1,LEN(__Word),1),"Letter",MID(__Word,[Value],1))
        )
    VAR __FilterTable = 
        INTERSECT(
            SELECTCOLUMNS(__WordSearchTable,"Letter",[Letter]),
            SELECTCOLUMNS(__ClientTable,"Letter",[Letter])
        )
    VAR __Table1 = FILTER(__WordSearchTable,[Letter] IN __FilterTable)
    VAR __Table2 = 
        ADDCOLUMNS(
            FILTER(__WordSearchTable,[Letter] IN __FilterTable),
            "Consq",[Value] - MAXX(FILTER(__Table1,[Index]=EARLIER([Index])&&[Value]<EARLIER([Value])),[Value])
        )
    VAR __Table3 = FILTER(__Table2,[Consq]=1)
    VAR __Table = GROUPBY(__Table3,[Index],"Rows",COUNTX(CURRENTGROUP(),[Letter]))
    VAR __Max = MAXX(__Table,[Rows])
    VAR __WordIndex = MAXX(FILTER(__Table,[Rows]=__Max),[Index])
RETURN
    CONCATENATEX(FILTER(__WordSearchTable,[Index]=__WordIndex),[Letter],,[Value])

Anonymous The logic seems implementable because PHP and other programming languages have these things called loops. DAX does not have loops so breezing away the "oh just repeat this process" because extremely problematic. So, I will have to sit down with this and figure out once again how to emulate/simulate loops in DAX. It's not that I haven't done it but it takes a fair amount of effort and focus to get it right.

Also, the logic you describe would return "Barcalays ("

Anonymous - OK, in order to implement this I kind of had to twist your logic in order to make it compatible with DAX but I *believe* it accomplishes the result you want. And, as I mentioned, it returns "Barclays (" for that one entry. Not entirely sure how to fix that unless you have an idea. I can't just wipe out spaces and there is no "trim" function in DAX to trim out special characters. I could potentially create a table of characters to remove at the end of words and SUBSTITUTE them out somehow if they are at  the end of a word but not sure of the ramifications of that because you might have a client whose name you want to have a special character at the end of their name. Could also potentially wipe out an ending space " " but, again, not sure if perhaps a situation might arise where you would want to wipe out two trailing spaces.

Anyway, I never ask for Kudos but I expect Kudos, it's Labor Day after all and I'm here coding fuzzy text matching logic in DAX afterall.

Updated PBIX is attached, Table (24). Here is the DAX:

Column 4 = 
    VAR __WordSearchTable = 
        GENERATE(
            'Table (24b)',
                VAR __Word = MAXX(FILTER('Table (24b)',[Index]=EARLIER([Index])),[Ongoing Projects])
            RETURN ADDCOLUMNS(GENERATESERIES(1,LEN(__Word),1),"Search",LEFT(__Word,[Value]))
        )
    VAR __Table =
        FILTER(
            ADDCOLUMNS(
                __WordSearchTable,
                "Match",SEARCH([Search],[Client Name],,BLANK())
            ),
            NOT(ISBLANK([Match]))
        )
    VAR __Max = MAXX(__Table,[Value])
    VAR __Match = MAXX(FILTER(__Table,[Value]=__Max),[Search])
RETURN
    __Match

Anonymous - Just increase your matching threshold. It is the GENERATESERIES statement where it currently starts at 1, set the threshold to be 5 for example. So, it will need to match at least 5 characters to be considered a match. I actually called this out in the slightly improved version I posted to the Quick Measures Gallery.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Fuzzy/td-p/1352914

Otherwise, open to advice on how you want the algorithm to work. It is fuzzy matching after all, hard to eliminate 100% of the false positives. It kind of goes with the territory. Might be possible to do some kind of logic where it tries to evaluate position of the match within the Client Name maybe? Could get hairy.

I just want to point out, I've spent hours and hours on this thing, not a single Kudo. Just saying.

Anonymous - What if we set a second threshold and if the match is below that threshold it must be an exact match? What do you think about that approach? Now that I understand the issue, I'll take a look at building this out with all of your data and see what I can do.

Anonymous Here is an implementation of the logic I proposed. PBIX is attached below sig with your full dataset. It has the CleanMatchThreshold set to 5 but I think it works better at 4

Fuzzy Column = 
    VAR __MatchThreshold = 3
    VAR __CleanMatchThreshold = 5
    VAR __WordSearchTable = 
        GENERATE(
            'Ongoing Projects',
                VAR __Word = MAXX(FILTER('Ongoing Projects',[Index]=EARLIER('Ongoing Projects'[Index])),[Ongoing Projects])
            RETURN ADDCOLUMNS(GENERATESERIES(3,LEN(__Word),1),"Search",LEFT(__Word,[Value]))
        )
    VAR __Table =
        FILTER(
            ADDCOLUMNS(
                __WordSearchTable,
                "Match",SEARCH([Search],[Column1],,BLANK())
            ),
            NOT(ISBLANK([Match]))
        )
    VAR __Max = MAXX(__Table,[Value])
    VAR __Match = MAXX(FILTER(__Table,[Value]=__Max),[Search])
    VAR __Clean1 = IF(RIGHT(__Match,1)="(",LEFT(__Match,LEN(__Match)-1),__Match)
    VAR __Clean2 = IF(RIGHT(__Clean1,1)=" ",LEFT(__Clean1,LEN(__Clean1)-1),__Clean1)
RETURN
    IF(
        LEN(__Clean2)<=__CleanMatchThreshold,
        IF(
            LEN(__Clean2) = LEN([Column1]),
            __Clean2,
            BLANK()
        ),
        __Clean2
    )

Anonymous - Further improvement

Fuzzy Column = 
    VAR __MatchThreshold = 3
    VAR __CleanMatchThreshold = 4
    VAR __WordSearchTable = 
        GENERATE(
            'Ongoing Projects',
                VAR __Word = MAXX(FILTER('Ongoing Projects',[Index]=EARLIER('Ongoing Projects'[Index])),[Ongoing Projects])
            RETURN ADDCOLUMNS(GENERATESERIES(3,LEN(__Word),1),"Search",LEFT(__Word,[Value]))
        )
    VAR __Table =
        FILTER(
            ADDCOLUMNS(
                __WordSearchTable,
                "Match",SEARCH([Search],[Column1],,BLANK())
            ),
            NOT(ISBLANK([Match]))
        )
    VAR __Max = MAXX(__Table,[Value])
    VAR __Match = MAXX(FILTER(__Table,[Value]=__Max),[Search])
    VAR __Clean1 = IF(RIGHT(__Match,1)="(",LEFT(__Match,LEN(__Match)-1),__Match)
    VAR __Clean2 = IF(RIGHT(__Clean1,1)=" ",LEFT(__Clean1,LEN(__Clean1)-1),__Clean1)
RETURN
    IF(
        LEN(__Clean2)<=__CleanMatchThreshold,
        SWITCH(TRUE(),
            LEN(__Clean2) = LEN([Column1]),__Clean2,
            SEARCH(__Clean2,[Column1],,0)=1,__Clean2,
            BLANK()
        ),
        __Clean2
    )

Anonymous - How about this version? I have implemented a smarter matching algorthim taking into account how much of the total client name is matched. Also, there is an exception process in the SWITCH statement where you can call out specific matches, such as the example, "ABB". So if ABB is matched, it returns it. You could add additional rows to the SWITCH statement for other exceptions. I have the exception commented out because it is not needed with the current thresholds but left it there as an example. Updated PBIX attached.

So, basically the new rules are FuzzyThrehold1 = 30%, if 30% of the length is matched and it starts at the beginning, match. FuzzyThreshold2, if more than 80% of the total length is matched, it's a match.

Fuzzy Column = 
    VAR __MatchThreshold = 3
    VAR __CleanMatchThreshold = 5
    VAR __FuzzyThreshold1 = .3
    VAR __FuzzyThreshold2 = .8
    VAR __WordSearchTable = 
        GENERATE(
            'Ongoing Projects',
                VAR __Word = MAXX(FILTER('Ongoing Projects',[Index]=EARLIER('Ongoing Projects'[Index])),[Ongoing Projects])
            RETURN ADDCOLUMNS(GENERATESERIES(3,LEN(__Word),1),"Search",LEFT(__Word,[Value]))
        )
    VAR __Table =
        FILTER(
            ADDCOLUMNS(
                __WordSearchTable,
                "Match",SEARCH([Search],[Client Name],,BLANK())
            ),
            NOT(ISBLANK([Match]))
        )
    VAR __Max = MAXX(__Table,[Value])
    VAR __Match = MAXX(FILTER(__Table,[Value]=__Max),[Search])
    VAR __Clean1 = IF(RIGHT(__Match,1)="(",LEFT(__Match,LEN(__Match)-1),__Match)
    VAR __Clean2 = IF(RIGHT(__Clean1,1)=" ",LEFT(__Clean1,LEN(__Clean1)-1),__Clean1)
RETURN
    IF(
        LEN(__Clean2)<=__CleanMatchThreshold,
        SWITCH(TRUE(),
            //__Clean2 = "ABB",__Clean2,
            LEN(__Clean2) = LEN([Client Name]),__Clean2,
            LEN(__Clean2)/LEN([Client Name])>__FuzzyThreshold1 && SEARCH(__Clean2,[Client Name],,0)=1,__Clean2,
            LEN(__Clean2)/LEN([Client Name])>__FuzzyThreshold2,__Clean2,
            BLANK()
        ),
        __Clean2
    )

Anonymous - I would have to think about how to implement that within the program, but what about Ernst & Young, that would only match Ernst then? Perhaps I am not understanding something.

Anonymous - I do not see how that logic is going to prevent something like International from being mapped to National for example. International will map to National Grid up until national, no space or G and so...

With the setting of .18 for threshold 1, is the only outstanding exceptions these:

Anonymous - Yes, but "blah blah International blah" is still going to match the "National" in "National Grid" this way and be designated "National" in the match. I don't see that logic preventing this or explain where my thinking is flawed.

Are you saying that if the Ongoing Project has a space in it to only match if the match goes beyond the space? Because I'm not sure how to implement that although I have some thoughts. However, even so that still doesn't fix the problem below with WOLTERSKLUWERS.

I was able to resolve the National issue though with some additional matching threholding checks. The only thing that I see that is an exception at this point is that the client "WOLTERSKLUWERS" matches the "Wolters" in "Wolters Kluwer" when you probably want it to say "Wolters Kluwer". I don't know of a way around that problem. 

Updated PBIX attached. Here is the latest:

Fuzzy Column = 
    VAR __MatchThreshold = 3
    VAR __CleanMatchThreshold = 4
    VAR __FuzzyThreshold1 = .18
    VAR __FuzzyThreshold2 = .8
    VAR __WordSearchTable = 
        GENERATE(
            'Ongoing Projects',
                VAR __Word = MAXX(FILTER('Ongoing Projects',[Index]=EARLIER('Ongoing Projects'[Index])),[Ongoing Projects])
            RETURN ADDCOLUMNS(GENERATESERIES(3,LEN(__Word),1),"Search",LEFT(__Word,[Value]))
        )
    VAR __Table =
        FILTER(
            ADDCOLUMNS(
                __WordSearchTable,
                "Match",SEARCH([Search],[Client Name],,BLANK())
            ),
            NOT(ISBLANK([Match]))
        )
    VAR __Max = MAXX(__Table,[Value])
    VAR __Match = MAXX(FILTER(__Table,[Value]=__Max),[Search])
    VAR __Clean1 = IF(RIGHT(__Match,1)="(",LEFT(__Match,LEN(__Match)-1),__Match)
    VAR __Clean2 = IF(RIGHT(__Clean1,1)=" ",LEFT(__Clean1,LEN(__Clean1)-1),__Clean1)
RETURN
    IF(
        LEN(__Clean2)<=__CleanMatchThreshold,
        SWITCH(TRUE(),
            //__Clean2 = "ABB",__Clean2,
            LEN(__Clean2) = LEN([Client Name]),__Clean2,
            LEN(__Clean2)/LEN([Client Name])>__FuzzyThreshold1 && SEARCH(__Clean2,[Client Name],,0)=1,__Clean2,
            LEN(__Clean2)/LEN([Client Name])>__FuzzyThreshold2,__Clean2,
            BLANK()
        ),
        SWITCH(TRUE(),
            __Clean2 = "Blue Cross",__Clean2,
            LEN(__Clean2)/LEN([Client Name])<__FuzzyThreshold2 && SEARCH(__Clean2,[Client Name],,0)<>1,BLANK(),
            __Clean2
        )
    )

Anonymous Except it doesn't work that way. To implement the original algorithm in DAX I had to do this:

Let's take just the first 2 client names

In order to emulate looping, which is impossible in DAX, this becomes:

and so on, the entire list of projects gets blown out into a big table like this. Then, I go about adding a column to this table that says whether it matches the client or not, basically a 1 or a 0. Then, I filter out the zeros. Then I grab the MAX of the second column, that is my longest match.

This is looping in DAX.

So, explain again how your logic fits into this?

Steelwave is a Client Name and it matches Sterling up until 3 characters. The only match returned is Sterling, not Sterling and Steelwave because Steelwave is not an Ongoing Project name. So, you are left with Steelwave matching Sterling up until Ste. You can lower the thresholds but then you start running into issues with Dell, etc.

Anonymous - OK, this might actually be the one. I added a KillThreshold that was possible because I moved the cleaning to the end to maximum the length of the match. I got rid of the "Unit" match also. So, basically once I adjusted the cleaning of the special characters and spaces at the end, I was able to increase Threshold1 so that ABB is included but Unit is excluded. The only special exception is for Blue Cross and I'm not sure how else to handle that one quite honestly. Let me know if you find any obvious issues. Updated PBIX attached.

Fuzzy Column = 
    VAR __MatchWord = [Client Name]
    VAR __CleanMatchThreshold = 4
    VAR __KillThreshold = 3
    VAR __FuzzyThreshold1 = .4
    VAR __FuzzyThreshold2 = .8
    VAR __WordSearchTable = 
        GENERATE(
            'Ongoing Projects',
                VAR __Word = MAXX(FILTER('Ongoing Projects',[Index]=EARLIER('Ongoing Projects'[Index])),[Ongoing Projects])
            RETURN ADDCOLUMNS(GENERATESERIES(3,LEN(__Word),1),"Search",LEFT(__Word,[Value]),"Original",__Word)
        )
    VAR __Table =
        FILTER(
            ADDCOLUMNS(
                __WordSearchTable,
                "Match",SEARCH([Search],__MatchWord,,BLANK())
            ),
            NOT(ISBLANK([Match]))
        )
    VAR __Max = MAXX(__Table,[Value])
    VAR __Match = MAXX(FILTER(__Table,[Value]=__Max),[Search])
    VAR __Proposed =
        IF(
            LEN(__Match)<=__CleanMatchThreshold,
            SWITCH(TRUE(),
                //__Clean2 = "ABB",__Clean2,
                COUNTROWS(FILTER(__Table,[Value]=__Max))>1,BLANK(),
                LEN(__Match) <= __KillThreshold,BLANK(),
                LEN(__Match) = LEN(__MatchWord),__Match,
                LEN(__Match)/LEN(__MatchWord)>__FuzzyThreshold1 && SEARCH(__Match,__MatchWord,,0)=1,__Match,
                LEN(__Match)/LEN(__MatchWord)>__FuzzyThreshold2,__Match,
                BLANK()
            ),
            SWITCH(TRUE(),
                __Match = "Blue Cross" || __Match = "Blue Cross ",__Match,
                LEN(__Match)/LEN(__MatchWord)<__FuzzyThreshold2 && SEARCH(__Match,__MatchWord,,0)<>1,BLANK(),
                __Match
            )
        )
        VAR __Clean1 = IF(RIGHT(__Proposed,1)="(",LEFT(__Proposed,LEN(__Proposed)-1),__Proposed)
        VAR __Clean2 = IF(RIGHT(__Clean1,1)=" ",LEFT(__Clean1,LEN(__Clean1)-1),__Clean1)
RETURN
    __Clean2

Anonymous - So, yes, we can return the entire matching project, that we can do. I will see what I can do with that. The problem with Blue Cross is that the client names are so long that it ends up not meeting the threshold requirements for a match. I may be able to adjust those. So, for example 

Blue Cross and Blue Shield of Arizona I

Matches 11 out of like 40 characters or .275% This makes it fall below thresholds set to filter out other stuff. The Ernst & Young entry for Blue Cross is even worse.

Anonymous - I will take a look.

Anonymous Glad we got there, was an interesting problem. There are still replies in this thread you haven't kudo'd... 🙂

This may be my record for longest thread before solution, 6 pages!!!

 Ge

Greg_DecklerI dont see any changes in this screenshot as well (even checked the file).

Here is the exact problem statement.

Replace Table 24(a) Barclays with Barclays (ABC)

Replace Table 24[Client Name] Casey's General with CaSey General

Replace Table24[Client Name] ASTRAZENCALimited with 2020 ASTRAZENCALimited AMT

Now I want the result to show as Barclays, Casey, Astrazenca

The result should be the complete set of matching characters including space and special characters if any and not the value from the Table 24(a). (For ex: Casey)

I am not really sure if we need to implement fuzzy search. I have not explored much of it.

Greg_DecklerYou see the problem? I have the project name in 24(a) as Casey's (note the apostrophe) which is same as Barclays (ABC).
If I replace 24(a) with Casey's then the result column will not return Casey because the apostrophe is missing in CaSeY General (Table 24). Same goes with Barclays since (ABC) is missing from Table 24.

My data is such that the project list is not the exact match with the Client Name. Some projects are of the above nature. Hence I need to match the continuous set of strings and then return that set as the result.

So result for Casey's and Barclays (ABC) would be Casey and Barclay respectively considering these set of continuous strings are present in Table 24.

I hope I was able to explain my problem. I am not sure if there is a code but I wonder what logic does the filter search (ctrl+shift+L) follow to be able to achieve this.

Anonymous - What you are asking for is not likely very possible or you are not explaining it very well. What is the "threshold" for a match? I asked originally if you wanted fuzzy matching and you said "no". Well, what you are asking for is called fuzzy matching. Fuzzy matching is when you match based upon a threshold value. If you want fuzzy matching, you are going to need to use Power Query's built in fuzzy matching in Merge query. I could probably come up with some convoluted DAX way of doing it. It would probabl involve Text to Table (https://community.powerbi.com/t5/Quick-Measures-Gallery/Text-to-Table/m-p/1312929#M594). I feel like you intentionally wasted my time on this and on a holiday weekend no less because I asked if you wanted fuzzy matching and you said no and then what you want turns out to be fuzzy matching. But I would have NEVER gone down this path had you been clear that you want fuzzy matching. It honestly makes no sense to write a fuzzy matching DAX solution and believe me I am the king of writing ridiculously impractical DAX but that is probably even a bridge too far for me.

In conclusion. Use what you want is called fuzzy matching. Use the built-in fuzzy matching technology in Power Query Merge (Table.Join). The end.

Greg_DecklerThank you for your time Greg. I really appreciate it. My apologies if you felt we wasted our time. Let me try the fuzzy matching feature and see if that is of any help in solving my requirement. 

Anonymous - You understand the problems with trying to reinvent this wheel in DAX, right? You can't just match up until a certain number of letters from the start because you have things like LLP Ernst and Young and Ernst and Young. You can't match a certain number of characters from the right because of things like BARCLAYS (LLP) versus Barclays (ABC). You can't use CONTAINSTRING or FIND or SEARCH because we are talking about partial (fuzzy) matching. You would literally have to tear apart each string into it's own table using Text to Table (word version) where every character gets a row in a table. No problem for the Client Name but then you are trying to compare it against every single other word in the other table. Except DAX has no looping. So, you could brute force it if you have a set amount of words in your second table, but something tells me the real world implementation of this is not going to have 5 rows in the second table but more like a few hundred or thousand. So, you would have to do text to table on a hard coded table several hundred or thousand times and it would be hard coded. You *might* attempt to do something tricky by using an Index and trying to use GENERATE against the Index and somehow transforming each of search words into a table of one column and somehow tie it all together with a UNION but I haven't really ever figured that how to do that dynamically without hard coding everything. Then, after you have thus far figured out how to do 2 or 3 already impossible things, then and only then you are ready to start trying to match sequences of letters. Here you would have the enviable task of iterating through your text to table of all your search words and checking each row individually whether it matched the a character in the text to table of your Client Name. OK, get rid of the characters (rows) that didn't match. Now, iterate over this table again looking for consequetive rows. Iterate again to find more than some threshold of consequetive rows. Somehow magically extract those rows from the table. Return them.

Other than somehow overcoming probably 2 or 3 impossible things, and I don't use the word impossible lightly when it comes to DAX, sure, the method above will work perfectly. Oh, except it would grind the CPU of your computer into an absolute dust pile of silicon for more than maybe a dozen words and search words. Other than that, first rate solution. The above thought process is what went through my head when I first saw this problem which was the reason for the question about fuzzy matching. Because I already knew that fuzzy matching was not in the cards. Definition of fuzzy matching: Fuzzy matching is a technique used in computer-assisted translation as a special case of record linkage. It works with matches that may be less than 100% perfect when finding correspondences between segments text.

Greg_DecklerThanks for your explanation. The Fuzzy match aint working as expected. For example: Client_name is "BARCLAYS CAPITAL". This client name is getting matched with Project_name - "TriState Capital" instead of Barclays (ABC) with 0.24 threshold (the least I could set for a maximum output).

With threshold of 1 , BARCLAYS CAPITAL has no matches and returns NULL. 

Due to the above, I really doubt if the results with Fuzzy matching are accurate for my datasets. I do not want a wrong project to be mapped to a client name.

Let me figure out something, if nothing works out then I should probably write to the management to tweak the data source to match with the Client Name without the (ABC) or apostrophe  🙂

Thanks again for your time.