Forum Discussion
Helper ICompare two tables in power query
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.
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.
2 Replies
- Zubair_Muhammad
Community Champion
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"
dpbiHi @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.