Find and replace comma separated values

[Jeffrey T]

Hello Everyone,

I was provided an Excel sheet that has users in one column and the companies they belong to in another column, some with more than one group.

username	company
john.smith	The Tire Store, Garage  Hut, Nova Barns
jane.doe	Free Store

The issue is the company names are not the actual names but partial name. I have another Excel sheet with the right names and their company codes.

CompanyCode	CompanyName
TTS	The True Tire Store
RGH	The Real Garage Hut
TFS	The Free Store

I would like to be able to do a fuzzy lookup and replace of the values from the first table in the values from the second table. I was thinking of a fuzzy lookup and replace or something along those lines.

Any assistance is much appreciated.

[#2]

@Jeffrey T 

You can try using the Fuzzy matching option in the Merge Queries function in Power Query.

I created two queries for your tables - data and lookup (respectively).

Then this:

let
    Source = data,

    split 
        = Table.ExpandListColumn(
            Table.TransformColumns(
                Source,
                {{"company", Splitter.SplitTextByDelimiter(", ")}}),
            "company"
        ),

    fuzzy 
        = Table.FuzzyNestedJoin(
            split,{"company"}, 
            lookup, {"CompanyName"}, 
            "lookup",
            JoinKind.LeftOuter, 
            [IgnoreCase=true, IgnoreSpace=true, NumberOfMatches=1, Threshold=0.5]
        ),

    expand 
        = Table.ExpandTableColumn(
            fuzzy,
            "lookup",
            {"CompanyCode", "CompanyName"},
            {"CompanyCode", "CompanyName"}
        ),

    mergedcompany 
        = Table.AddColumn(
            expand,
            "mergedCompanyName",
            each if [CompanyName] = null then [company] else [CompanyName]
        ),

    group 
        = Table.Group(
            mergedcompany,
            {"username"},
            {
                {"Original", each Text.Combine([company],", ")},
                {"Replaced", each Text.Combine([mergedCompanyName],", ")}
            }
        )
in
    group

This is what the result looks like:

[#3]

Hi @Jeffrey T 

A possible approach with Power Query function Table.FuzzyNestedJoin and options:
    IgnoreCase=true, IgnoreSpace=true, Threshold=0.5

If more than 1 match the query keeps the one with highest similarity

[#4]

Just realized there's a little mistake (unecessary step) in the query I earlier shared. This doesn't seem to matter though...