change text value of one column as other column value

[Anonymous]

Hello ,
I have two different tables 1(data table) 2(standard table) , i want dealer name from data table to be same as mentioned n standard table to make a join .sharing an image with dummy data.
Thank you in advance

 

[tamerj1]

Hi @Anonymous 
The example provided seems to be over simplified. This is one is easy to handle but I guess the real data is much bigger with much more variations. Please provide a descent amount of editable sample data that really reflects all the possible cases.

[Anonymous]

Hello @tamerj1 ,
Thank you for your reply , sharing link to the editable file 
https://docs.google.com/spreadsheets/d/1tZd7UxrheU39KNfTJXsqXyT9T3tJpgYc/edit?usp=sharing&ouid=10564... 

[tamerj1]

@Anonymous 

code is required to access the file 

[Anonymous]

@tamerj1 you can access the file now

[tamerj1]

Hi @Anonymous 
Please refer to attched sample file with the proposd solution.

Dealer (Standard) = 
VAR String1 = Table1[Dealer]
VAR Items1 = SUBSTITUTE ( String1, " ", "|" )
VAR Length1 = COALESCE ( PATHLENGTH ( Items1 ), 1 )
VAR T1 = GENERATESERIES ( 1, Length1, 1 )
VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items1, [Value] ) )
VAR T3 = 
    ADDCOLUMNS ( 
        Standard,
        "@%Match",
        VAR String2 = Standard[Dealer]
        VAR Items2 = SUBSTITUTE ( String2, " ", "|" )
        VAR Length2 = COALESCE ( PATHLENGTH ( Items2 ), 1 )
        VAR T4 = GENERATESERIES ( 1, Length2, 1 )
        VAR T5 = SELECTCOLUMNS ( T4, "@Item", PATHITEM ( Items2, [Value] ) )
        VAR T6 = INTERSECT ( T5, T2 )
        RETURN
            DIVIDE ( COUNTROWS ( T6 ), COUNTROWS ( T5 ) )
    )
VAR T7 = TOPN ( 1, T3, [@%Match] )
RETURN
    MAXX ( T7, Standard[Dealer] )

[Anonymous]

I have shared complex data is other sheet of the file ,Plwase check 

[tamerj1]

@Anonymous 

Same solution is working. I have updated the sample file my the original reply with the detailed data. Seems to work just fine. Please refer to my original reply for the updated sample file

[Anonymous]

@tamerj1 please send me a request for an access