Data Cleansing Advice Needed

Anonymous,

You can solve it in below way:

1. As because there is no common logic between rows then you need to create a dictionary by yourself. So, create a table called 'Dictionary' which will store all correct machine names as below:

(example - link)

2. Create a new blank query and call it fnFuzzyLookup. Then paste below Power Query code there:

(Input as text) => 
let 
    ListCount = List.Count(Dictionary[Brand]),  
    Result = List.Generate( () => 
                            [
                                i = 0,
                                t = Dictionary[Brand]{i},
                                k = if Text.Middle(Input,0,Text.Length(t)) = t then 1 else 0
                             ],
                             each (if ([i] < ListCount and [k] < 2) then true else false),
                             each [
                                        i = [i] + 1,
                                        t = Dictionary[Brand]{i},
                                        k = if [k] = 1 then 2 else (if (Text.Middle(Input,0,Text.Length(t)) = t) then 1 else 0)  
                                    ],
                             each (if [k] = 0 then "" else [t])
    )
in
    List.Last(Result)

3. Go to the main table (query) and then Add column -> Invoke custom function:

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!