How to search for the same value in all other rows and then get adjacent column value

[justlogmein]

I have created a simple lookup that finds words that are CONTAINED in my current list by using a merge. I then check to see if they are full word matches using another column that returns TRUE or FALSE. The problem is that when I merge this list, I create duplicate rows where it finds multiple matches.

Below you can see I searched for 'flange' and 'bolt' which were both in a single description field. My Custom column then finds if the match is a full word match (case and comma insensitive). Only 'bolt' had a full match. I want to create another column that will return TRUE for the 'flange' row because there is another description exactlty the same that has TRUE in the Custom column.

Here is some M code with the Desired column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvL3CdFRcPNx9HN3ddFR8HCNAGJHFyUdJSBKy0nMS08FMtwcfYJdgXRIUKirUqwOAW1J+TklMMXE6slIrSBdSyKG02IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t, CAGECDXH = _t, Description.1 = _t, Custom = _t, Desired = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"CAGECDXH", type text}, {"Description.1", type text}, {"Custom", type logical}, {"Desired", type logical}})
in
    #"Changed Type"

[Syndicate_Admin]

Hi,

You may use Text.Contains function.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvL3CdFRcPNx9HN3ddFR8HCNAGJHFyUdJSBKy0nMS08FMtwcfYJdgXRIUKirUqwOAW1J+TklMMXE6slIrSBdSyKG02IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t, CAGECDXH = _t, Description.1 = _t, Custom = _t, Desired = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom.1", each Text.Contains([Description],[Description.1],Comparer.OrdinalIgnoreCase)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Description", type text}, {"CAGECDXH", type text}, {"Description.1", type text}, {"Custom", type logical}, {"Desired", type logical}})
in
    #"Changed Type"

[xzmiche]

Hi,

You may use Text.Contains function.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvL3CdFRcPNx9HN3ddFR8HCNAGJHFyUdJSBKy0nMS08FMtwcfYJdgXRIUKirUqwOAW1J+TklMMXE6slIrSBdSyKG02IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t, CAGECDXH = _t, Description.1 = _t, Custom = _t, Desired = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom.1", each Text.Contains([Description],[Description.1],Comparer.OrdinalIgnoreCase)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Description", type text}, {"CAGECDXH", type text}, {"Description.1", type text}, {"Custom", type logical}, {"Desired", type logical}})
in
    #"Changed Type"

[justlogmein]

Thanks for the replies, however I think we are looking at two different problems. I don't want to change the logic for how I am recognising matches, that is working fine. I just want to search for other descriptions that match the current row and if any of them have TRUE in the Custom column, then return TRUE in another.