Data Cleaning in Power BI? Learn How to Do it!


Share on facebook
Share on twitter
Share on pinterest
Share on linkedin

Do you think that your data is ready to make relevant business reports or for statistical analysis in MS Power BI? Most probably, your answer will be ‘No’. So, let’s understand how data sanitization and data cleaning in Power BI is important for your business and how it can impact your decision-making process and overall business flow.

Since enterprises could have a huge data comprising relevant as well as irrelevant. To make informed decisions, enterprises are required to have prepared data, and data sanitization in Power BI is the process that prepares your system to make valuable visual insights and analytics.

When your data is finally imported to the Power BI system, the next step is to clean the data. This will help you to make visual reports from the cleaned and relevant data only. If you avoid this step, you will not get accurate and expected visualizations. Now, let’s understand why data cleaning in Power BI is an important part of data analytics.

Importance Of Data Cleaning In Power BI

Just importing the data into the Power BI system is not enough to make powerful reports. It may interrupt your reporting and visualizations or may give errors. Let’s consider that you have imported your data from various available data sources, now, you need to examine what makes your data unprepared for further data analysis.

During examining, you could have numerous issues interrupting your analysis:

  • A column that could have alphabetical data contains only numerical values.
  • There could be several columns with errors and null values.
  • If duplicated entries are made for, will appear in multiple columns
  • In case, an address column contains street, city, zip code, etc.

Thus, leveraging such type of inserted data, when you create reports & visuals, you get incorrect or bad results that may not help your business at all. For instance, you may receive incorrect reports about inventory levels, mismatching data about total sales, or similar to this.

Here, Power BI with its Power Query feature offers businesses a powerful data cleansing environment to ensure data preparation for sound analytics and correct visual data results.

transform data in power bi
Image Source

How To Clean & Transform Unstructured Data in Power BI

To structure your data, you need to start by selecting the ‘Transform Data’ option available on the ‘Home’ tab in your Power BI Desktop. See above reference image.

The data in your select query will be displayed just in the middle of the Power Query Editor screen and available queries can be seen in the Queries pane in the table form. The steps that you make to transform the data are recorded in the Query setting pane. Now, whenever the specific query connects to the specified data source, it will apply your by-default steps.

Now, let’s move to the core part to understand the step-by-step process to transform the unstructured data:

Manage Column Headers

The very first step is to manage your initial data i.e., to identify and correct the column headers and column names within your source data as well as ensuring whether each column is at the right place.

For instance, we are considering two files here. One is a CSV file as a data source, and another is representing that how data is imported when you have unmanaged columns and names.

In the first image, here is sample data for sales targets categorized by products and sub-categorized by month. However, as per the second image data couldn’t be imported as expected. It’s difficult to read due to the undescriptive names of columns, here you need to reorganize the data.

identify columns
identify headers names

Recommended Reading:

Promote Headers

Since Power Query Editor considers all the data in table rows, thereby, when you create a table using Power BI Desktop and if the data source is containing column names, this may result in inaccurate data. This data inaccuracy can be sorted out by determining the first table row into column headers. For doing so, you need to apply the power query ‘Use First Row as Header’ by going through the drop-down list of column1 or by simply selecting the ‘Home’ tab.

02 use first row headers ssm

Here, the below image demonstrates how applying the ‘Use First Row as Header’ feature shapes your data:

02 use first row headers ssm 1

Rename Columns

The next step is to examine the column headers. For instance, one or more columns contain wrong headers, misspelled header(s), or lack consistency in header name series.

To solve this, the above image can be considered wherein you can see the impact of ‘Use First Row as Header’. You can refer to the column containing ‘Name’ as a subcategory, now it belongs to the column header which is incorrect.

To rename this column header, you are required to right-click the header-> select ‘Rename’-> ‘edit’ the name->press ‘Enter’.

Remove Top Rows & Columns

When you perform Power BI data cleaning, you may discover some blank rows or containing data that you do not require for your reports. Here in the below image, there is a blank row that can be removed. Similarly, if you do not want data in the second row, this can also be removed. Simply follow:

Home tab-> ‘Remove Rows’-> ‘Remove Top Rows. For clarification, see the visual representation below:

Considering the same image, you can remove columns too by following:

‘Home tab-> ‘Remove Columns’-> ‘Remove Columns’.

02 remove top rows ssm
02 remove top rows feature ssm

Read Also: Power BI Architecture – 7 Important Components Explained

Unpivot Columns

Unpivoting Columns is the most common feature in Power BI. Leveraging this feature, you can import data from any available data source but most often users use this feature when they are required to import the data from the Excel file.

Let’s understand in a depth via an example image:

02 excel data multiple columns ss

When your data is imported to Power Query, it will look like this:

02 original data ss

Certainly, this reveals sales separately for each year, but it could be difficult to create a report containing total sales for both the years 2018 and 2019. Considering this as a goal. Let’s see how your report will look like in Power BI:

02 unpivot ss

To achieve such a sales report, you are required to rename the first column to month and then highlight the year 2018 and 2019 columns, then select ‘Transform’ tab and lastly ‘Unpivot’. Further, you can rename the Attribute and Value column to Year and Sales amount consecutively.


Thus, this blog reveals how data cleansing, managing, and structuring are required to seamlessly create visual reports, analytics, and other key metrics. Hope this blog will help you clean your redundant and shape your unstructured data. If this blog makes you curious to understand in depth about Power Query or seeking to implement Power BI to unlock growth potential, contact Dynamics Square, we are here to help.


Share on facebook
Share on twitter
Share on pinterest
Share on linkedin

Top of The Week

Looking for Dynamics 365 Business Applications?

Talk to our Dynamics 365 expert to get the best solutions for your business.