Power Query - Fuzzy Match, Concat Matches

Miguel de las Nieves 0
Viewed 34

I need see if the values in a column (cargo) in table called Transaction Table match the Item keywords in a table called Categories table.

If there is a match with the Item keywords, the corresponding Category to that Item should be added to a new column in the Transaction table called.
If there are multiple matches, all the categories should be added in the column separated by a column with no repeating categories.

Here are the tables.

This dummy data in an google sheets file can be found here: https://docs.google.com/spreadsheets/d/1HVohp0SgMZ9B39F2l36IJ0tGDLmoGB3cfc9-BNk-QiU/edit?usp=sharing

Thanks in advance

powerquery powerbi
1 Answers

enter image description here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVPRbsIwDPwVq89ItFUH7NG0hoaGJHJS+oD4/9+YE7GxqSRTX67S+XL22fd7VTfberdt22pT9cisiKGp5eeIRj4PaAZwhOyrx+Yt+0OwHy15uOJAYE+gCcNIXCpoa2A6sToTY7B58X3kQi8k0RfpSS0qx91FbE1AZbz0AMYGOlo7ZcU7wcXW0iA8qgGChac0PAeTrRF8ZiKTJsc0wCLm+UramlfRft2mRi/IsTJCzzKTpa6GXthpIspMFwr/1h0ENy1cVW/ZLniToG6UN/QZN0DPBCetzmMAP6tQdN+kVh0y9uMcKGs/5elmdppe7KIyK0eroe/X0cfVeBEO63c7eVBrCv7XjsIF+4lCti6O7V1+b+R/EkmZK0NZcv3cu5Ia/rmPHDP2fZQ8DaQLLNGivehuGVWgsj/B37eT40Ts5Bh8SPHE9agejy8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Carrier = _t, Cargo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Carrier", type text}, {"Cargo", type text}}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Cargo"}, Categories, {"Item"}, "Categories", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.6]),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Text.Combine( List.Distinct( [Categories][Category]),",") ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Categories"})
in
    #"Removed Columns"
userjpYe10
Related