Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

v-lili6-msft
Community Support
‎05-31-2021 01:56 AM

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

 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: 

v-lili6-msft_0-1622450711706.png

 

Expected result： 

v-lili6-msft_1-1622450711708.png

 

Detailed steps： 

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

v-lili6-msft_2-1622450711712.png

 

 

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”:

v-lili6-msft_3-1622450711720.png

 

 

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

v-lili6-msft_4-1622450711721.png

 

 

  1. Remove the original [City] column:

v-lili6-msft_5-1622450711722.png

 

 

 

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

v-lili6-msft_6-1622450711725.png

 

 

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

v-lili6-msft_7-1622450711728.png

 

 

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

v-lili6-msft_8-1622450711731.png

 

 

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

v-lili6-msft_9-1622450711737.png

 

 

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

v-lili6-msft_10-1622450711745.png

 

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

v-lili6-msft_11-1622450711752.png

 

 

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

v-lili6-msft_12-1622450711758.png

 

 

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

v-lili6-msft_13-1622450711764.png

 

 

  1. Remove column [Attribute]:

v-lili6-msft_14-1622450711767.png

 

 

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

v-lili6-msft_15-1622450711773.png

 

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

v-lili6-msft_16-1622450711774.png

 

 

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

v-lili6-msft_17-1622450711775.png

 

 

Reference links: 

Table.AddFuzzyClusterColumn - PowerQuery M | Microsoft Docs

 

 

Author: Robert Qin

Reviewer: Ula Huang, Kerry Wang

PQ Blog.pbix
0
Comments