Forum Discussion

ngocnguyen's avatar
ngocnguyen
Icon for Helper IV rankHelper 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 tex...
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.

ngocnguyen's avatar
ngocnguyen
Icon for Helper IV rankHelper 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 !

 

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
5 years ago

ngocnguyen 

 

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

 

  • ngocnguyen's avatar
    ngocnguyen
    Icon for Helper IV rankHelper IV
    5 years ago

    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's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      5 years ago

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

        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