PQ - Comparing two columns in the same table and returning unmatched

[sandy666]

(question from the web (https://www.mrexcel.com/board/threads/comparing-two-columns-in-the-same-table-and-returning-unmatched.1161386/))

Looking to find all the rows where column B does NOT match column A


SourceResult

ColumnAColumnBData1Data2ColumnANotMatchBData1Data2

USNTSUSNTSABC
123USNTSUSSPZABC
123

USNTSUSSPZABC
123USNTSUSLVVABC
123

USNTSUSLVVABC
123USNTSUSLVVABC
123

USNTSUSLVVABC
123USNTSUSLVVABC
123

USNTSUSLVVABC
123USNTSUSLVVABC
123

USNTSUSLVVABC
123USNTSUSLVVABC
123

USNTSUSLVVABC
123USNTSUSLVVABC
123

USNTSUSLVVABC
123USNTSUSLVVABC
123

USNTSUSLVVABC
123USNTSUSLVVABC
123

USNTSUSLVVABC
123USLVVUSNYCABC
123

USMSYUSMSYABC
123

USHOUUSHOUABC
123

USMSYUSMSYABC
123

USMSYUSMSYABC
123

USLVVUSNYCABC
123

USNYCUSNYCABC
123


let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Diff = Table.AddColumn(Source, "NotMatchB", each List.Difference({[ColumnB]},{[ColumnA]})),
Expand = Table.ExpandListColumn(Diff, "NotMatchB"),
Filter = Table.SelectRows(Expand, each ([NotMatchB] <> null)),
TSC = Table.SelectColumns(Filter,{"ColumnA", "NotMatchB", "Data1", "Data2"})
in
TSC