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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors