Data Preparation and Cleaning is the process of transforming raw, unstructured or messy data into clean, organized & usable datasets for analysis. Power BI uses the Power Query Editor for these tasks. Our data has to be clean so we can come up with trends and predictions accurately.
Power Query is a powerful software used for data preparation and cleaning and even transformation. It enables users to connect to various data sources, reshape data by applying a wide range of transformations, such as changing data types, filtering rows and columns and merging tables and then load the cleaned data into Power BI Desktop for further analysis and reporting.
The Power Query Interface has ribbon, just by clicking around the ribbon, you’ll see there are many ways to transform your data. The Home, Transform, Add column, View, Tools tabs of the ribbon. The Home, Transform, and Add column tabs are the most used ribbon.
The Home Tab
The Home tab in Power Query is where you’ll find the most commonly used actions for connecting to data, managing queries and transforming data. The functions include New source, Enter data, Data source settings, Remove rows, Close and Apply as you can see in the above image.
The Transform Tab
The Transform tab in Power Query is where you find tools for cleaning, reshaping, and preparing our data for analysis. It provides options such as Changing data type, Split column, Group by, Pivot column and many more, as seen in the image above.
The Add Column Tab
The Add Column tab in Power Query provides various options for creating new columns based on existing data. It includes Conditional column, Custom column, Column from examples, Duplicate column, Index column as seen in the above image.
Key Steps In Data Preparation and Cleaning
- Import Dataset into Power BI Desktop: To import data, you go to the home tab, you’d see “ Get Data” as shown below then get your data from the Excel workbook, CSV, or any data source shown.
- Open Power Query Editor by clicking transform data: After we have gotten our data, we’d need to clean our data for easy visual presentation and we’d use our Power Query Editor for that by clicking “Transform” icon.
- Examine data quality in Power Query Editor: assess data quality & identify issues.
In the image below we can see that the data is 100% valid and has 0% error.
- Remove/Replace errors & missing rows and columns: Eliminate rows containing errors using the “remove rows” option. Eliminate unnecessary columns or rename columns for clarity.
This image has lots of null/empty rows and columns, we need to remove them
Here we have removed the columns and rows, now sheet3 data is now 100% valid.
- Change data type: Ensure data types are consistent and appropriate for analysis by using the “change type” option. When we want to change the data type, we right click on the Column Header and see Change data type on the list of options and change it to the right data type for better analysis.
- Handle duplicates: Identify & remove duplicate rows/columns using the “remove duplicates” options. We use the Duplicate option to remove repeated names, etc.
- Close & Apply: Once the data is cleaned and transformed, click “close &apply” to save the changes and load the data into Power BI.
Steps Taken During The Data Preparation and Cleaning
All the steps we have taken to clean our data are all shown at the right corner.
