Forum Discussion

NSBS's avatar
NSBS
Helper I
4 years ago
Solved

Comparing 2 tables in M Language

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 ListComparison
AFTRUE
ALTRUE
DDFALSE
DZTRUE
ASTRUE
ADTRUE
AGFALSE

 

Country Code Reference

Country Code Reference
AF
AL
DZ
AS
AD

 

Need Help
Tutorial Requests
  • PC2790's avatar
    PC2790
    4 years ago

    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

     

3 Replies

  • PC2790's avatar
    PC2790
    Community Champion
    4 years ago

    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's avatar
      NSBS
      Helper I
      4 years ago

      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 ListComparison
      AFTRUE
      ALTRUE
      DDFALSE
      DZTRUE
      ASTRUE
      ADTRUE
      AGFALSE
      • PC2790's avatar
        PC2790
        Community Champion
        4 years ago

        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