Power Query Tips: How to clean up spelling mistakes and typos

This is the start of a blog series providing you with tips for data preparation within the Power Query interface and advice for using M code to achieve this!

Setting the scene:

You have a dataset which has spelling mistakes of the same value. For instance, the participants have entered their country of origin with different spellings and typos in the example below. You would want to clean these values so only one form of spelling represents each unique value.

How to achieve this:

1. We will use the Table.AddFuzzyClusterColumn function. The syntax works as below:

Table.AddFuzzyClusterColumn(table as table, columnName as text, newColumnName as text, optional options as nullable record) as table

check out this page to explore the syntax and options further.

Table.AddFuzzyClusterColumn - PowerQuery M

Learn more about: Table.AddFuzzyClusterColumn

Microsoft LearnDougKlopfenstein

Edit the M Code:

1. First, create a new blank query and insert the Table.AddFuzzyClusterColumn after the equals.
2. Then reference the query name, the column you want to fix the spelling for and then the new name of the column for the fixed results.

Here is the code below to copy:

= Table.AddFuzzyClusterColumn(#"Store Survey Results - Question Sheet", "Country", "New Country")

Ta-dah, you now have a query with the table and the fixed spelling mistakes for the selected column.