Comparing 2 tables in M Language

[NSBS]

Hi,

I have 2 tables as per below and I want to create a M code which compare the Country List Table with the Country Code Reference Tabel and give me the Comparison Column as the result (True is Country List row is in the Country Reference Code).

Kindly help on the M code. Thank you

Country List Table

Country List	Comparison
AF	TRUE
AL	TRUE
DD	FALSE
DZ	TRUE
AS	TRUE
AD	TRUE
AG	FALSE

Country Code Reference

Country Code Reference

AF

AL

DZ

AS

AD

[PC2790]

Hey @NSBS ,

You can make use of merging the two tables and get the value of "Comparison" using Left Outer Join.

Here is the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRTitUBUj5gyiUKwguGUC5KsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Country List" = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"Country List"}, #"Country List Table", {"Country List"}, "Country List Table", JoinKind.LeftOuter),
    #"Expanded Country List Table" = Table.ExpandTableColumn(#"Merged Queries", "Country List Table", {"Comparison"}, {"Country List Table.Comparison"})
in
    #"Expanded Country List Table"

Result will be like this:

I  hope this is what you are looking for

[NSBS]

Hi @PC2790 ,

The result I'm looking for is as per below. And I cannot do the merging of the two tables as there is a lot more calculation has been done into original table (Country List Table)
Is there any way for us to create a new custom column (named Comparison) and produce result as per below.

Country List	Comparison
AF	TRUE
AL	TRUE
DD	FALSE
DZ	TRUE
AS	TRUE
AD	TRUE
AG	FALSE

[PC2790 · Accepted solution]

I interpreted your requirement wrongly. You can add a new column in Country List table as below:

= Table.AddColumn(Source, "Match", each List.Contains(#"Country Code Reference"[Country List], [Country List]), type logical)

Here is the code to do it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRTitUBUj5gysUFQkVBBIMhFETQ0V0pNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Country List" = _t]),
    Custom1 = Table.AddColumn(Source, "Match", each List.Contains(#"Country Code Reference"[Country List], [Country List]), type logical)
in
    Custom1