cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Correct misspelled words in Power Query

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

 

20201021172728.png

 

Thanks!

1 ACCEPTED SOLUTION
Solution Specialist
Solution Specialist

Hi, @JackLi ,

 

You can use Table.AddFuzzyClusterColumn() ,

 

@Daniil has a great blog post for that.

 

Best.

 

Mark this post as solution if this helps.

View solution in original post

3 REPLIES 3
Super User III
Super User III

Hi, @JackLi , 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"

Screenshot 2020-10-21 160903.png

 

Super User III
Super User III

Hi @JackLi 

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.

Solution Specialist
Solution Specialist

Hi, @JackLi ,

 

You can use Table.AddFuzzyClusterColumn() ,

 

@Daniil has a great blog post for that.

 

Best.

 

Mark this post as solution if this helps.

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors