How to fuzzy match the dirty data and horizontal display the corresponding table

[v-lili6-msft]

 Scenario:    

In this scenario, the column [City] in my table has some dirty data with some space and case errors. It's also a mixture of abbreviations and full names of the cities.

I want to find a way to unify the value of the column ‘City’ into the abbreviation of the cities, and merge this abbreviation into the column name of the table to display the table data horizontally with Forecasted Gross and Forecasted Net.

  

The original status of the main table is like below: 

Expected result： 

Detailed steps： 

1. Go to power query editor, click on “Enter data” and create a Transformation table like this:

2.  Go to the “Queries”->Table, click on the “Advanced Editor”, enter the part of M code in box after the step “Changed Type” and before the operator “in”:

Click on “Done” and you will see a new column like this:

3. Remove the original [City] column:

3.  Click on the column [City_Expanded]->”Unpiovt columns”:

4. Click on the column [Forecasted Gross]->”Unpiovt columns”:

5. Click on the column [Forecasted Net]->”Unpiovt columns”:

6. Click on “Add custom column” and enter the column [Value] in the formula:

7. Press on “Ctrl” and click on the column [Value] and [Attribute.1], then click on “Merge columns” and set it like this:

8. Press on “Ctrl” and click on the column [Custom] and [Attribute.2], then click on “Merge columns” and set it like this:

9. Click on the column [Merged], select “Pivot Column”-> select values column as ”Value.1”-> select Aggregate function as ”Sum”:

10. Click on the column [Merged.1], select “Pivot Column”-> select values column as ”Value.2”-> select Aggregate function as ”Sum”:

11. Remove column [Attribute]:

12. Select the column [ID] and click on “Group By”, then set the table grouping like this:

13. Click on “Close&Apply” to import the data model into Power BI.

Finally, we can get the expected output in the Power BI like this:

 

Reference links: 

Table.AddFuzzyClusterColumn - PowerQuery M | Microsoft Docs

Author: Robert Qin

Reviewer: Ula Huang, Kerry Wang

PQ Blog.pbix