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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors