Cleaning data in Power BI with Power Query Editor

Introduction

Power Query Editor is used for cleaning and transforming data in Power BI. Below are examples of data cleaning tasks performed in this editor.

How to open Power Query editor

1. Go to the Home tab in Power BI

2. Select Transform data in the Queries section

Power Query Editor opens in its own separate window. 

Note that all transformation steps are local to Power Query Editor. To get the changes back to Power BI Desktop, you must click the ‘Close & Apply’ button in the top left hand corner.

Promoting the first row of data as column headings

• Tables are called Queries on the left hand side.

• Select the table you’d like to edit

In the Home tab, find the Use First Row as Headers button 

Renaming a column 

1. Double click a column header

2. Rename, then press enter

Duplicating a column

1. Right click the heading of a column

2. Select duplicate column

Replacing values in a column

1. Right click a cell value in a column

2. Click Replace Values…

3. Fill out Replace With, then press OK

Choosing columns

1. In the Home tab, select Choose Columns

2. Unselect columns you do not wish to keep

This gives more control over removing columns entirely, as you retain other columns you may need for later analysis.

Sorting columns

1. Select the drop down arrow of the column you’d like to sort

2. If you get an error that says ‘List may be incomplete’, this is because a limited amount of data is loaded into Power Query Editor. Click ‘Load more’.

3. Sort based on your task.

You may also filter the States to to show a particular set, by unticking (Select All) and ticking your desired States.

All applied steps in Power Query Editor are filled in the right hand bar. If you want to delete a step, hover over the step then click the red X.

Checking cleanliness of a column

1. Click on the View tab

2. In Data Preview select 'Column quality'

This will display how much of your columns are valid, has errors or empty values.