Forum Discussion
Compare two columns in two tables
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
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 Custom1In 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"
2 Replies
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 Custom1In 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"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.