Create new table in Power Query with data that meet certain conditions from other tables

Hi Marek12345 

You can create refer to the following queries.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci7KLEktykxUMFTSUXKGkkZg0hRMminF6iApM8JQZgxVjKLMGEOZOZBEVWOCpMYYaheItFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Criteria = _t, #"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Criteria", type text}, {"Column 1", type text}, {"Column 2", type text}, {"Column 3", type text}, {"Column 4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Criteria"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Combine(Table.SelectRows(#"Filtered Rows",(x)=>x[Criteria]=[Criteria])[Value],",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Added Custom1" = Table.AddColumn(#"Removed Duplicates", "Custom.1", each 1)
in
    #"Added Custom1"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVIwVNJRcgaSsTooAkboAsboAiboAqboAmYIASN0W4zQbTFCN8MI3QxjdGtN0A01QTfUBN3pJuiGQgQslGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Users = _t, Conditions = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Users", type text}, {"Conditions", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, Query2, {"Custom.1"}, "Query2", JoinKind.LeftOuter),
    #"Expanded Query2" = Table.ExpandTableColumn(#"Merged Queries", "Query2", {"Criteria", "Custom"}, {"Criteria", "Custom.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Query2", "Custom.2", each let a=Text.Split([Custom.1],","),
 b=List.Count(a),
 c=List.Distinct(Table.SelectRows(#"Expanded Query2",(x)=>x[Users]=[Users])[Conditions]),
 d=List.Count(List.Intersect({a,c}))
 in if b=d then "Yes" else "No"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Conditions", "Custom", "Custom.1"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Criteria]), "Criteria", "Custom.2")
in
    #"Pivoted Column"

Output

Best Regards!

Yolo Zhu

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