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 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.

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"