Forum Discussion
Solved
Merge Queries
Dear All
I would like to merge these two queries based on Product Line and Commodity Name. The Fuzzy option didn't work for me. As you can I have specific text that is identical. Would like help on how I could merge the two queries and have an added column in Query 1 that brings me the Commodity Code in Query 2.
Query 1
Query 2
Results should be as follows:
At threshold level of 0.2, it will match. See the sample code
Query1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zcw7CoAwEEXRrQxT27kCEdQmKFhKikFfJJCPZNw/ijaC9T3cZeEOUQK44lZOpWkaZ2pSYlt9kpE9Qf0JknWFai4e+hDzgiFHUHOUnMgBG8o/xiN451GUerlHNVt7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Gender = _t, #"Product line" = _t]), #"Merged Queries" = Table.FuzzyNestedJoin(Source, {"Product line"}, Query2, {"Commodity Name"}, "Query2", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, NumberOfMatches=1, Threshold=0.2]), #"Expanded Query2" = Table.ExpandTableColumn(#"Merged Queries", "Query2", {"Commodity Code"}, {"Commodity Code"}) in #"Expanded Query2"
1 Reply
At threshold level of 0.2, it will match. See the sample code
Query1
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zcw7CoAwEEXRrQxT27kCEdQmKFhKikFfJJCPZNw/ijaC9T3cZeEOUQK44lZOpWkaZ2pSYlt9kpE9Qf0JknWFai4e+hDzgiFHUHOUnMgBG8o/xiN451GUerlHNVt7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Gender = _t, #"Product line" = _t]), #"Merged Queries" = Table.FuzzyNestedJoin(Source, {"Product line"}, Query2, {"Commodity Name"}, "Query2", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, NumberOfMatches=1, Threshold=0.2]), #"Expanded Query2" = Table.ExpandTableColumn(#"Merged Queries", "Query2", {"Commodity Code"}, {"Commodity Code"}) in #"Expanded Query2"