Search for non unique values in multiple columns on a different table and return matching value.

[PurdieB]

Hello,

I have the two tables below (Records & Requirements) and I"m trying to create the 3rd table (Results) to display which "Requirement" the employee has from the list of options in the Requirments Table and then identify if there are any missing requirements.  

Seems like this should be a simple lookup function but nothing I've tried is getting me what I need. 

Please help!

Records Table

Employee	Jobcode	Requirement
A	121	AB
A	121	DL
B	121	AC
C	121	AD
D	545	AB

Requirements Table:

Jobcode	Req 1a	Req 1b	Req 1c	Req 2a
121	AB	AC	AD	DL
545	QQ

Results Table:

Employee	Jobcode	Requirement 1	Requirement 2
A	121	AB	DL
B	121	AC	MISSING
C	121	AD	MISSING
D	545	MISSING

[CNENFRNL · Accepted solution]

let
    Requirements = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVNJRcnQCEc4gwgVIuPgoxepEK5mamAI5gYFAAopiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Jobcode = _t, #"Req 1a" = _t, #"Req 1b" = _t, #"Req 1c" = _t, #"Req 2a" = _t]),
    Records = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MgSSjk5KsTrIAi4+YAEnhApnsIAzQsAFLOACZJqamELNiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Jobcode = _t, Requirement = _t]),
    #"Grouped Rows" = Table.Group(Records, {"Employee", "Jobcode"}, {{"Req", each [Requirement]}}),
    Merged = Table.NestedJoin(#"Grouped Rows", "Jobcode", Requirements, "Jobcode", "req", JoinKind.LeftOuter),
    Intersection = Table.AddColumn(Merged, "rq", each List.Intersect({[Req], Record.ToList([req]{0})},Comparer.OrdinalIgnoreCase)),
    #"Extracted Values" = Table.TransformColumns(Table.RemoveColumns(Intersection, {"Req","req"}), {"rq", each Text.Combine(_, "#(tab)"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "rq", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"rq.1", "rq.2"})
in
    #"Split Column by Delimiter"