Correct misspelled words in Power Query

[Anonymous]

How do I clean the data like the figure below? I didn't find a similar function in the PQ interface.

Thanks!

[CNENFRNL]

Hi, @Anonymous , you might want to try the following code,

let
    typo = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk/NycxLL8nPU4rVAfOg3GIUfiZCOh9FNYwXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t]),
    misspelling = Table.FromList({"Welington City","CityWellingtons","City Wellingtin 443","Nr. 22 WellongtonCity"}, Splitter.SplitByNothing(), {"Addr"}),
    #"Added Custom" = Table.AddColumn(misspelling, "Custom", each
        List.Accumulate(
            typo[City], _[Addr], (s, c) => Text.Replace(s, c, "Wellington")
        )
    )
in
    #"Added Custom"

[PhilipTreacy]

Hi @Anonymous 

Alternatively, look for a commonality in the text and then create a Conditional Column

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if Text.Contains([City], "lingto", Comparer.OrdinalIgnoreCase) then "Wellington" else null)
in
    #"Added Conditional Column"

Phil 

---

If I answered your question please mark my post as the solution.

If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[DavisBI · Accepted solution]

Hi, @Anonymous ,

You can use Table.AddFuzzyClusterColumn() ,

@Daniil has a great blog post for that.

Best.

Mark this post as solution if this helps.