Look up value if they contains specific value of other column

[daicaboy]

Hello,

I want to Look up value in table A if they contains specific value in column of table B. (Just contain, don't need the same value)

Ex:

Table A

Product	Color Code
A	001
B	002,0001,003
C	004,005
D	005

Table B

Color Code	Color
001	Blue
002	Red
003	Green
004	Yellow

The result:

Color Code	Color
A	Blue
B	Red,Blue,Green
C	Yellow
D	None

Thank for your time!

[daicaboy]

Thank you, 

I really appreciate it.

[dufoq3 · Accepted solution]

Hi @daicaboy, another approach:

Result

let
    TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIwMFSK1YlWcgKzjXQMgAJAwhgs6AwWNAHyTcF8FzAfyI4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Color Code" = _t]),
    TableB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRcsopTVWK1QFxjYDcoNQUKM8YyHMvSk3Ng/JNgPzI1Jyc/HKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Color Code" = _t, Color = _t]),
    BufferedTableB = Table.Buffer(TableB),
    Ad_Color = Table.AddColumn(TableA, "Color", each Text.Combine(Table.Combine(List.Transform(Text.Split([Color Code], ","), (x)=> Table.SelectRows(BufferedTableB, (y)=> Text.Contains(x, y[Color Code]))))[Color], ","), type text),
    ReplacedValue = Table.ReplaceValue(Ad_Color,"","None",Replacer.ReplaceValue,{"Color"}),
    RemovedColumns = Table.RemoveColumns(ReplacedValue,{"Color Code"})
in
    RemovedColumns

[AlienSx]

let
    dict = Record.FromList(TableB[Color], TableB[Color Code]),
    color = Table.TransformColumns(
        TableA, 
        {"Color Code", (x) => Text.Combine(
            List.Transform(
                Text.Split(x, ","), 
                (w) => Record.FieldOrDefault(dict, w, "None")
            ), 
            ","
        )}
    )
in
    color

[Ahmedx]

pls try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIwMFSK1YlWcgKzjXSAfCA2Bos5g8VMgHxTMN8FzAeyYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Color Code" = _t]),
    dict = List.Buffer(Table.ToList(TableB,(x)=>x)),
    f = (x) =>[
      a = List.Accumulate(dict, x, (s, c) =>  Text.Replace(s, c{0}, c{1})), 
        
      b =  if a = x then "None" else Text.Combine(  List.Select( Text.Split( a,","),(z)=> not  Text.Contains(z,"00")),",")
    ][b],
    Custom1 = Table.TransformColumns(Source,{"Color Code",f})
in
    Custom1