Data Cleaning in Power BI

What is Data Cleaning in Power BI?

Data cleaning in Power BI refers to the process of cleaning the data and preparing it for accurate data analysis and reporting in Power BI. Data cleaning can help you in the identification of errors in the data field, data inconsistencies, data quality issues, and any anomalies that may create issues in the final output and cause incorrect or misleading data analysis or reporting.

Data cleaning involves several steps that include handling null or unexpected values, eliminating any duplicate values, transforming data fields into appropriate formats, using the correct data types in Power BI, and removing erroneous data to prepare the data for analysis and visualization.

Data cleaning in Power BI has the following advantages that include:

• Generate correct values by performing data aggregations and calculations through measures or calculated columns.
• Organize data in tabular format, thereby assisting users in querying and accessing data interactively.
• Remove duplicate in excel data from the raw dataset, optimizing the search and storage.
• Split data fields into simple columns or merge multiple columns into a single column, enhancing the user readability
• Store data as per the user-accessible formats.
• Replace errors and missing values.
• Resolve data quality issues.

Data Cleaning Functions in Power BI

Some of the key data-cleaning functions in Power BI include:

• You can eliminate the occurrence of repeated or the same values in a data field in your dataset to retain only distinct values.
• You have the flexibility of keeping or removing the rows as per your requirements, for example, Keeping or removing top rows, bottom rows, alternate rows, etc.
• It enables you to split a single column into multiple columns to store data based on various parameters such as delimiters, characters, positions, digit to non-digit, etc.
• You can use this feature to replace a data field with user-defined values and format a column to a specific data type.
• Use this feature to combine files and transpose rows into columns and vice versa.
• Use this feature to display the last rows of the table as first.

In the next section, we will see how you can clean data in Power BI

How to Clean Data in Power BI?

To clean data in Power BI, follow the instructions outlined below:

Step 1: Import your dataset and load it into the Power BI Desktop.

Step 2: Once the data is loaded successfully, navigate to the Data pane. Right-click on the table or click on the ellipsis (…) and select the Edit query option from the context menu. This will navigate you to the Power Query Editor window.

Step 3: Analyse the dataset using statistics and distribution, and identify the issues with the data using the Power Query Editor.

Step 4: Implement the data cleaning functions depending on the issues identified to clean and prepare the data.

Step 5: Review the transformed data and amend changes if necessary.

Step 6: Save the changes by using the Close & Apply button in Power Query Editor.

Examples

In this section, we will understand data cleaning in Power BI through some examples.

Example #1 

In this example, we will apply data cleaning in Power BI using uncleaned bike sales data. uncleaned bike sales data contains missing values, inconsistent data types, and duplicate rows.

To clean data in Power BI, follow the steps below:

Step 1: Import your dataset and load it into the Power BI Desktop.

Step 2: Navigate to the Data pane. Right-click on the table and choose Edit query from the context menu.

It will open the dataset in Power Query Editor.

Step 3: In the Power Query Editor, select the data columns of the Bike Sales table and click on Data Type to format the columns.

Here, we have selected Whole Number for the Sales_Order# column.

It will open up a confirmation window.

Step 4: Click on Replace current to replace the existing values as per the chosen format.

You will notice that there are specific erroneous rows after changes to the data type.

Step 5: Click on the Remove Rows – Remove Errors. 

You can also hover your mouse over a column and click on Remove Errors.

This will eliminate any erroneous records from the table. You can view the steps in the APPLIED STEPS section.

Step 6: Select all the columns and click on the Replace Values.

It will open up a dialogue window. Enter the value you want to replace with and click on OK.

Now you will notice that all the null values are replaced with the target value provided, i.e., 0.

Step 7: Repeat the same step for other columns to replace any null values.

Post replacement, you will see the updated values in your dataset.

Step 8: Select a column and click on Split Column – By Delimiter.

Step 9: Specify the Delimiter and click on OK. Here we have provided the delimiter as (.

It will split the Age_Group column into Age_Group.1 and Age_Group.2 columns.

Step 10: Select the Age_Group.2 column and replace the right brace to make it meaningful information.

It will remove the right brace from the values in the column Age_Group.2. You can also view the same in APPLIED STEPS.

Step 11: Click on Close & Apply to save the changes.

Example #2

In this example, we will apply data cleaning in Power BI using uncleaned electronics Price. uncleaned electronics Price contains inconsistencies and missing values.

To clean data in Power BI, follow the steps below:

Step 1: Import your dataset and load it into the Power BI Desktop.

Step 2: Navigate to the Data pane. Right-click on the table and choose Edit query from the context menu.

It will open the dataset in Power Query Editor.

Step 3: In the Power Query Editor, select the first data columns of the Uncleaned electronics Price table and right-click on the column Unnamed.0. Select Rename from the context menu to provide a valid name.

You will see the renamed column along with the steps in the APPLIED STEPS section.

Step 4: Select all the columns in the table and choose Remove Rows – Remove Blank Rows.

This will remove the rows with null or blank values.

Step 5: Click on the Remove Rows – Remove Errors.

You will see all the erroneous records have been eliminated from the table.

Step 6: Select the Price column and choose the Data Type to Fixed decimal number.

This will format the Price column to decimal values.

Step 7: Click on Close & Apply to save the changes.

Example #3

In this example, we will apply column quality, column profile, and column distribution features in Power Query Editor using the online_courses_uncleaned dataset to identify any data cleaning requirements. online_courses_uncleaned contains the raw data on various online courses offered to students by different universities.

To clean data in Power BI, follow the steps below:

Step 1: Import your dataset and load it into the Power BI Desktop.

Step 2: Navigate to the Data pane. Right-click on the table and choose Edit query from the context menu.

It will open the dataset in Power Query Editor.

Step 3: In the Power Query Editor, navigate to the View tab. Under the Data Preview pane, choose Column quality, Column distribution and Column profile.

It will display detailed information on column values along with the column distribution.

Step 4: In the Power Query Editor, select the columns and format the data type.

You will see some of the erroneous values for the transformed column.

Step 5: Select Remove Rows – Remove Errors to eliminate any erroneous records.

You can see all the error values are now removed from the dataset.

Step 6: Select all the columns and choose the Replace Values option. Provide the target value to replace with and click on OK. This will replace all the null values in the dataset.

Step 7: Navigate to the View tab and select Column quality. Now you can see all the empty values are assigned with some values.

Step 8: Click on Close & Apply to save the changes.

Important Things to Note

• Data cleaning is a periodic and regular process. You must regularly review the data quality plan and optimize the data cleaning steps to ensure accurate and meaningful insights.
• It is always recommended that you document your data cleaning processes for future reference and standardization.
• If you are using the column profile and distribution in Power Query, by default, it only examines the first 1000 rows in your dataset.
• Consider the impact of replacing the missing or null values in your final output. Incorrect values may create outlier or inconsistent results.
• You should backup the original data before making any significant changes.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Data cleaning in Power BI. Here we explain how to clean data in power bi, advantages, functions, examples, and points to remember. You may learn more from the following articles –

• Power BI Data Modeling
• Power BI Timeline
• Power BI Aggregate Function