Forum Discussion
Comparing 2 tables in M Language
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
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
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 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
