Return value based on a selection table

[brief001]

I have a table that shows what colors a record contains. Next, a selection table must be looked at to determine what value the record will receive.
It is possible that several selections will suffice, in which case it will be the choice with the lowest priority number.

Record 1 contains the colors red and white, and according to the selection table this record will be given the value red. This is because record 1 contains the color red.
Record 2 contains the colors yellow, green and purple, and according to the selection table this record will be given the value yellow-green. This is because record 2 contains the colors yellow and green.
Record 3 contains the colors brown, orange and black, and according to the selection table this record will be given the value brown-orange. This is because record 3 contains the colors bronw and orange.
Record 4 is a special one, just like record 3 it contains the colors brown and orange. But also contains the color blue. And via the selection table also contains exlude columns, which if blue appears, then further search is required. As a result, record 4 will have to get the value blue.
Record 5 is also special. This contains blue and red. And therefore meets two options according to the selection table. If multiple options are found, then the value must be given with the lowest priority number. In this case, the value will turn red.
Record 6 contains colors that do not appear in selection table. In this case, the value unknown must be given.

--- --- --- --- --- --- ---

Editor example voor table : Fact_table

let
Bron = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNzi9KUTBU0gEyU5RidVCEwjMyS1KRBY2AgpGpOTn55eii7kWpqXnoggGlRQU5KAYYA0WdivLL89AF/YsS89IxleYkJmcjC5pg02+CVT9YaU4pipgpDjE0v5th87sZkodiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Content = _t]),
#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"ID", type text}, {"Content", type text}})
in
#"Type gewijzigd"


Editor example voor table : Selection_table

let
Bron = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpKTQGSWBFIKlYnWskIyI5MzcnJLwcy3ItSU/OwKoco0YUoAOkzBgo6FeWXg5T7FyXmpafC1cJMccopRQiC1epCVYIMMMFrH1wSpNQU3TA0BJaLjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Priority = _t, Contains_1 = _t, Contains_2 = _t, Contains_3 = _t, Contains_4 = _t, Exclude_1 = _t, Exclude_2 = _t, Exclude_3 = _t, Exclude_4 = _t, Value = _t]),
#"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"Priority", Int64.Type}, {"Contains_1", type text}, {"Contains_2", type text}, {"Contains_3", type text}, {"Contains_4", type text}, {"Exclude_1", type text}, {"Exclude_2", type text}, {"Exclude_3", type text}, {"Exclude_4", type text}, {"Value", type text}})
in
#"Type gewijzigd"

[v-cazheng-msft]

Hi @brief001,

Not very clear about the calculated logic of Record 4 and Record 5. Could you please make it clearer? Thanks in advance!

Best Regards,

Community Support Team _ Caiyun