Comparing two columns in the same table, and returning unmatched.

[Richard U]

I'm looking to find all the rows where column B does NOT match column A

COLA	COLB	Data1	Data2
USNTS	USNTS	ABC	123​
USNTS	USSPZ	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USMSY	USMSY	ABC	123​
USHOU	USHOU	ABC	123​
USMSY	USMSY	ABC	123​
USMSY	USMSY	ABC	123​
USLVV	USNYC	ABC	123​
USNYC	USNYC	ABC	123​

So, The output should look like this

USNTS	USSPZ	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USNTS	USLVV	ABC	123​
USMSY	USMSY	ABC	123​
USLVV	USNYC	ABC	123​

I've been hacking around on this all day. This is my third day using power BI, and I could use a little help (Yes, I'm out of my depth)

 

[alansidman]

With Power Query
1. Load your table to the PQ editor (found on the Data Tab as Get and Transform Data)
2. Remove column B from the table.
3. Copy the table and delete the steps so that it looks like the original
4. Remove column A from the table.
5 Join the two tables you now have with a Right Anti join.

Result:

Book7
	A	B	C
1	Table2.COLB	Table2.Data1	Table2.Data2
2	USSPZ	ABC	123
Merge1

Mcode for first table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COLA", type text}, {"COLB", type text}, {"Data1", type text}, {"Data2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"COLB"})
in
    #"Removed Columns"

Mcode for the second table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COLA", type text}, {"COLB", type text}, {"Data1", type text}, {"Data2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"COLA"})
in
    #"Removed Columns"

Code for the Anti Join

let
    Source = Table.NestedJoin(Table1, {"COLA"}, Table2, {"COLB"}, "Table2", JoinKind.RightAnti),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"COLB", "Data1", "Data2"}, {"Table2.COLB", "Table2.Data1", "Table2.Data2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"COLA", "Data1", "Data2"})
in
    #"Removed Columns"