Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
NSBS
Helper I
Helper I

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

 

1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

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

 

View solution in original post

3 REPLIES 3
PC2790
Community Champion
Community Champion

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:

PC2790_0-1649736356231.png

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

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors