Compare two tables in power query

dpbi

Hi,

You can try this one.

Please see attached file with your sample data

let
    Source = Table.NestedJoin(#"Table A",{"Custom"},#"Table B",{"Custom"},"Table B",JoinKind.LeftOuter),
    #"Added Custom1" = Table.AddColumn(Source, "Custom.1", each Table.SelectColumns([Table B],{"Col1","Col2","Col3"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Table B"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each Table.ToList([Custom.1])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Custom.1"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns1", "Custom"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Col1 match", each if Text.PositionOf([Custom],[Col1])>-1 then 1 else 0),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Col2 Match", each if Text.PositionOf([Custom],[Col2])>-1 then 1 else 0),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Col3 Match", each if Text.PositionOf([Custom],[Col3])>-1 then 1 else 0),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "All matches", each Text.From([Col1 match]+[Col2 Match]+[Col3 Match]) & " matches"),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom6",{"Col1 match", "Col2 Match", "Col3 Match", "Custom"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns2", {"Index", "Col1", "Col2", "Col3", "All matches"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"All matches"]), "All matches", "Count"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Index", "Col1", "Col2", "Col3", "0 matches", "1 matches", "2 matches"})
in
    #"Reordered Columns"

Hi @Zubair_Muhammad

Thank you very much for your kind and fast help.

Your solution works perfectly. exactly what I needed.

Thank you also for the uploaded file, it helped me understand the steps in your solution.

As always , one of the best features in Power Bi Desktop is this forum.

Best regards

Dan.