Compare two columns in two tables

[Anonymous]

Hey,

I'm looking solution for following situation.

Table1

ID, Name, Role
122, Dave, Manager
123, Maria, CEO
124, Carl, Sales
125, Matt, Gardener

Table2

ID, Name, Colour
123, Maria, Green
124, Lucy, Blue
122, Dave, Red
125, Matt,

Result

Table1

ID, Name, Role, Colour
122, Dave, Manager, Red
123, Maria, CEO, Green
124, Carl, Sales, Unknown
125, Matt, Gardener, Unknown

So want to compare Table2 "ID & Name" to Table1 "ID & Name" and write "Colour" column value to Table1 new column. if "Colour" value is missing from Table2 or Table1 "ID & Name" are not found from Table2 write "Unknown".

Any help would be great

[Anonymous]

Hi @Anonymous ,

Please paste the following M in Advanced Editor dialog for Table1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyUtJRckksSwVSvol5iempRUqxOiAJY7BIUWYikHZ29YeKmoB4iUU5QCo4MSe1GCpsClZcUgKk3BOLUlLzQMbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Role = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Role", type text}}), 
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each if [Name]=(let currentID= [ID] in Table.SelectRows(Table2, each [ID] = currentID)){0}[Name] then (let currentID= [ID] in Table.SelectRows(Table2, each [ID] = currentID)){0}[Colour] else "Unknown"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Final Colour", each if Text.Length([Custom.1])=0 then "Unknown" else [Custom.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom.1"})
in
    #"Removed Columns"

Output:

Or use DAX , which is easier:

Colour(DAX) = 
var _find= LOOKUPVALUE(Table2[Colour],[ID],[ID],[Name],[Name],"Unknown")
return IF(_find=BLANK(),"Unknown",_find)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vijay_A_Verma · Accepted solution]

See the working here for Table1 - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyUtJRckksSwVSvol5iempRUqxOiAJY7BIUWYikHZ29YeKmoB4iUU5QCo4MSe1GCpsClZcUgKk3BOLUlLz4MaYg1TmZpZkwHTkJuYpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Role = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Role", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID", "Name"}, Table2, {"ID", "Name"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Colour"}, {"Colour"}),
    Custom1 = Table.ReplaceValue(#"Expanded Table2",each [Colour],each if [Colour]=null or [Colour]="" then "Unknown" else [Colour],Replacer.ReplaceValue,{"Colour"})
in
    Custom1

 In case, you need code for Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJR8k0sykwE0u5Fqal5SrE6IHETIN+nNLkSSDnllKZCRY2AXJfEslQgFZSaAhU0BRtRUgKklGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Colour = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Colour", type text}})
in
    #"Changed Type"