Power Query Merge Tables & Countif

[cr731]

I have two tables, Table A and Table B, where Table A contains all entries of Table B, but Table B does not contain all entries of Table A.

I want to merge these two together to get all values... which will basically be the same as Table A, but then I also want a column showing whether the value is on Table B or not.

What is the best way to do this in Power Query?

Thanks

 

[billszysz]

Try something like this

let
    TableA = Table.FromColumns({{"a","b","c","d","e","f"},{1,2,3,4,5,6}},{"Item","Value"}),
    TableB = Table.FromColumns({{"a","c","d","f"},{1,3,4,6}},{"Item","Value"}),
    Merge = Table.NestedJoin(TableA,{"Item"},TableB,{"Item"},"New Column"),
    Expand = Table.ExpandTableColumn(Merge, "New Column", {"Value"}, {"from TableB"}),
    Transform = Table.TransformColumns(Expand, {{"from TableB", each if _ = null then "Does not exist in TableB" else "Ok"}})
in
    Transform

Regards