Convert word with specific text

[ngocnguyen]

Hi

I have 2 tables as below

For column" Result" in Table1: Every row in column" Customer name"  that contain words mentioned in column" Ship to Party Name" in Table2  will be change into the text mentioned in " Convert" column

I use  the below fomulas:

=LOOKUPVALUE(Table2[Convert],Table2[Ship-to Party Name],LASTNONBLANK(filter(VALUES(Table2[Ship-to Party Name]),SEARCH(Table2[Ship-to Party Name],Table1[Customer name],1,0)),1)

However, it deliver to wrong result. For example, customer name" Mep Anking" convert to "Nguyen Kim", but it should be blank, not "Nguyen Kim"

Could you pls suggest me other workable formulas

Thanks

[v-janeyg-msft]

Hi, @ngocnguyen 

It’s my pleasure to answer for you.

According to your description,I think you can create a measure to calculate the desired result.

Like this:

Measure = 
var cname = MAX(Table1[Customer name])
return
MAXX (
    FILTER (
        Table2,
        FIND ( Table2[Snip-to Party Name], cname,,-1) > 0
    ),
    Table2[Convert]
)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ngocnguyen]

@v-janeyg-msft 

Many thanks for your support. However, I still not find the suitable solution.
Maybe my explaination is not clear that make you confusing.
So, i make another file for your understanding. (p/s: I hightlight red for some words that appear in " Ship to  party name" column in Table 2)
The "correct" column in Table 1 is that I wanna to get

In excel, I can get the result by combining Lookup & Countif, but in Power pivot  or Power query, I'm stuck with this

Pls support me in this case.Thanks !

[v-janeyg-msft]

@ngocnguyen 

Create a measure in PBI is easy, Why don’t you try it?

[ngocnguyen]

@v-janeyg-msft 

Yep, measure is ok, but your  suggested measure not yet solved my problem. Could you please suggest me another measure ?

[v-janeyg-msft]

Sorry, @ngocnguyen 

Your data NK, XNK may cause conflicts,and I entered the data with inconsistent case.

Try this:

Measure = 
var cname = MAX(Table1[Customer name])
return
MINX (
    FILTER (
        Table2,
        FIND (Table2[Snip-to Party Name], cname,,-1) > 0
    ),
    Table2[Convert]
)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ngocnguyen]

@v-janeyg-msft 

Thanks for your support. I do as the same your instruction. However, for case "XNK", the convert result is still" Nguyen Kim" While the correct result should be  blank T_T

[v-janeyg-msft]

Hi, @ngocnguyen 

Did you change the 'maxx' to 'minx' in the formula above?

Best regards

Janey Guo

[ngocnguyen]

@v-janeyg-msft 

Yes, I changed already but still not get the correct result. Could you pls send me your instance of power bi

Measure =

var cname = max(Table1[Customer name])
return
MinX (
FILTER (
Table2,
FIND (Table2[Ship-to Party Name],cname,,-1) > 0
),
Table2[Convert]
)

[v-janeyg-msft · Accepted solution]

Hi, @ngocnguyen 

Try this:

Measure =
VAR cname =
    MAX ( Table1[Customer name] )
RETURN
    MAXX (
        FILTER (
            Table2,
            FIND ( Table2[Snip-to Party Name], cname,, -1 ) > 0
                && LEN ( Table2[Snip-to Party Name] )
                    = IF (
                        LEN ( Table2[Snip-to Party Name] ) = LEN ( cname ),
                        LEN ( Table2[Snip-to Party Name] ),
                        MAXX (
                            SUMMARIZE (
                                Table2,
                                [Snip-to Party Name],
                                [Convert],
                                "length", LEN ( Table2[Snip-to Party Name] )
                            ),
                            [length]
                        )
                    )
        ),
        [Convert]
    )

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[msksenthil]

@LP2803 I hope you got the solution, if not please share the sample file that would be great to help you out.

Thanks

MS

[msksenthil]

@ngocnguyen 

I use the normal lookup function it returns blank only (sorry, please let me know if I missed anything)

[LP2803]

I agree with you. below is the sample that i did in my instance of powerbi

[ngocnguyen]

@msksenthil @LP2803 @amitchandak @CNENFRNL 

Many thanks for your support. However, I still not find find the suitable solution :(((((.
    
Many thanks for your support. However, I still not find the suitable solution.
Maybe my explaination is not clear that make you confusing.
So, i make another file for your understanding. (p/s: I hightlight red for some words that appear in " Ship to  party name" column in Table 2)
The "correct" column in Table 1 is that I wanna to get

[msksenthil]

@ngocnguyen  but 'Mep Anking' looks blank in the result ?

[ngocnguyen]

Yet, The results for "Mep Anking" should be blank as I showw in table. However, if I use my formular, the result is wrong as it shows" Nguyen Kim", not "Blank".  The reason is that Mep anking contain " nk" and my formulas maybe consider" nk" as "Nguyen Kim"

[amitchandak]

@ngocnguyen , try a new column like this in dax

maxx(filter(Table2,search(Table2[ship to party name], Table1[customer name],1,0)>0),Table2[convert])

[ngocnguyen]

@amitchandak ,

I try your formulas, but It still get wrong result as columns " Calculated column2"

The correct result should be as column " Correct Result"

[CNENFRNL]

Hi, @ngocnguyen , Power Query is more competent in such kind of data cleansing.  You might want to try this solution in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1NyUxUitWJVvJLL61MzVPIzsyFcL3BlHNOVkoOmFWRlw2mI/KyFXwzgSqdEkEisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Result",
        each List.Accumulate(
            Table.ToRecords(Table2),
            [Customer Name],
            (s,c) => if Text.Contains(s, c[#"Ship-to Party Name"], Comparer.OrdinalIgnoreCase) then c[Convert] else s
        )
    )

in
    #"Added Custom"