Forum Discussion

ngocnguyen's avatar
ngocnguyen
Helper IV
5 years ago
Solved

Convert word with specific text

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

 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    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.

18 Replies

  • msksenthil's avatar
    msksenthil
    Helper III
    5 years ago

    ngocnguyen 

     

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

     

    • LP2803's avatar
      LP2803
      Responsive Resident
      5 years ago

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

       

       

      • ngocnguyen's avatar
        ngocnguyen
        Helper IV
        5 years ago

        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

         

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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"

     

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      ngocnguyen
      Helper IV
      5 years ago

      amitchandak ,

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

       

      The correct result should be as column " Correct Result"

       

       

    • ngocnguyen's avatar
      ngocnguyen
      Helper IV
      5 years ago

      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"

  • msksenthil's avatar
    msksenthil
    Helper III
    5 years ago

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

    Thanks

    MS

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    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's avatar
      ngocnguyen
      Helper IV
      5 years ago

       

      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 !