Merge tables 2 columns matching 1

Hi GP85 ,

According to your description, here's my solution. Add a step in Table1 Advanced editor:

#"New"=let 
    filter=Table.SelectRows(Table2,each [Department]="Department 1")
in
    Table.SelectRows(#"Changed Type",each List.Contains(filter[Portfolio],[Portfolio]) or List.Contains(List.RemoveNulls(filter[Security]),[Security]))

Result:

Here's the whole M syntax:

Table2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsgvKknLz8nMVzBU0lFySS1ILCrJTc0rUTAHcoNTk0uLMksqgXKxOshqjVDVmgG5aCqMUVUYYqowwVQBt88ITa0pqlojTNPMCKowR1VhjKnCgqAKS1QVpshuNkZTa2hAMAgMDQnaaIgW1CbIVpqiK8YS6nDFJkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Portfolio = _t, Department = _t, Security = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Portfolio", type text}, {"Department", type text}, {"Security", type text}})
in
    #"Changed Type"

Table1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsgvKknLz8nMVzBU0lFSUIrVQRYzwiJmCRQLTk0uLcosqQQqQJU0RJY0RJdEkTVFkzUnZJcJmqQFFg2GBhDBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Portfolio = _t, Security = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Portfolio", type text}, {"Security", type text}}),
    #"New"=let filter=Table.SelectRows(Table2,each [Department]="Department 1")in
    Table.SelectRows(#"Changed Type",each List.Contains(filter[Portfolio],[Portfolio]) or List.Contains(List.RemoveNulls(filter[Security]),[Security]))
in
    #"New"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.