Introduction
Data cleaning is crucial step in the data preparation process that involves transforming and organizing raw data into a clean and structured format for analysis. Power query editor in power BI is a powerful tool that allows you to perform data cleaning tasks easily and efficiently.
Step 1 — Import Data into Power BI
- Open power BI desktop.
- Click on the home tab in the top menu.
- Click on “Get Data” and select your data source from the list.
- Follow the prompts to connect to your data source and load the data into power bi.
Step 2 — Open Power Query Editor
After importing the data, its time to open the power query editor to start the data cleaning process.
- In power BI desktop, click on “Transform Data” in the navigator dialog box.
- Power query editor will open, displaying your data in a tabular format.
Step 3 — Remove Unnecessary Columns
In many cases, your dataset may contain columns that are not required for your analysis. You can remove these unnecessary columns using power query editor.
- Select the columns you want to remove by clicking on their headers.
- Right click on the selected columns and choose “Remove”
Step 4 — Filter Data
Filtering data allows you to remove rows that are not relevant to your analysis, that contain erroneous values.
- Click on the drop-down arrow next to a column header to access the filter options.
- Choose the values you want to include or exclude from the dataset.
- Click on “OK” to apply the filter.
Step 5 — Remove Duplicates
Duplicates rows in your dataset can lead to inaccurate analysis results. Power query editor makes it easy to remove duplicate rows.
- Click on the drop-down arrow next to a column header to access the options.
- Click on “Remove Duplicates”
- Choose the columns based on which you want to identify duplicates or leave the default selection to consider all columns.
- Click on “OK” to remove the duplicates.
Step 6 — Split Columns
Sometimes a single column may contain multiple pieces of information that need to be separated. Power query editor allow you to split column based on delimeter or fixed position.
- Select the column, you want to split.
- Click on the transform tab in the power query editor ribbon.
- Choose “Split Column” and follow the prompts to split the data.
Step 7 — Replace Values
Data may contain inconsistent or incorrect values that need to be replaced. Power Query editor allows you to replace values easily.
- Select the columns you want to clean.
- Click on “Transform” tab in the power query editor ribbon.
- Choose “Replace Values” and provide the values which you want to replace and their corresponding replacement.
- Click on “OK” to apply the replacements.
Step 8 — Merge Queries
In some cases, you may have multiple data sources that need to be combined for analysis. Power query editor enables you to merge queries based on common columns.
- Click on “Home” in the power query editor ribbon.
- Click on “Merge Queries” and follow the prompts to merge the queries.
Step 9 — Close & Apply
Once you have completed the data cleaning steps in power query editor, its time to close and apply the changes to your power BI dataset.
- Click on “Close & Apply” in the power query editor ribbon.
- Power BI will apply the data cleaning steps to your dataset and load it into the power BI data model.
Step 10 — Visualize and Analyze Data
With your cleaned data in the power BI data model, you can now start creating visualizations and analyzing the data to gain valuable insights.
Conclusion
Data cleaning using power query editor in power BI can significantly improve the quality of your data and ensure more accurate and reliable analysis. In this step by step guide, you can efficiently clean your data, leading to more meaningful and insightful reports and dashboards in power BI.
