Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
DavisBI
Solution Specialist
Solution Specialist

Hi, @Anonymous ,

 

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
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2020-10-21 160903.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

PhilipTreacy
Super User
Super User

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


DavisBI
Solution Specialist
Solution Specialist

Hi, @Anonymous ,

 

You can use Table.AddFuzzyClusterColumn() ,

 

@Daniil has a great blog post for that.

 

Best.

 

Mark this post as solution if this helps.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors