Compare two tables in power query

[dpbi]

Hi.

Please advise how to get Power Query solution to the following:

I have tables ‘A’ and ‘B’.

I need to get table ‘C’.

 

Each row in table ‘A’  scans ( compare with )  each row in table ‘B’ and count matching items.

Basically, I need to add to table ‘A’ ,  4 custom columns for the 4 possible outcomes.

(In both tables, each letter can appear only once in a row and the order doesn’t matter).

For example , the calculation for the first row is done as follows:

With DAX I can do it by tranposing the rows (using Union and Row) and count the number of rows of the intersection (using Intersect), however, I need Power Query solution.

Best egards

Dan.

[Zubair_Muhammad · Accepted solution]

@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"

[Zubair_Muhammad · Accepted solution]

@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"

[dpbi · Accepted solution]

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.