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, Gr...
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.