top of page

How to Clean Data in Power BI and Its Importance.

I will talk about why data cleaning is required and how to perform that operation like,

  • Transform the Data

  • Data Formating

  • Set Header as the first row

  • Replace value (or correction of data)

Let us start with “Why”.

In simple words, whenever the executive needs to present the data, s(h)e needs to start the process of gathering data from multiple sources, followed by an investigation phase. It may have several problems with data such as missing values, duplicates, and incorrect column names. So executives need to clean, transform, and model the data. Every month, the executive follows the same steps for new data. It’s become quite a tremendous tedious task.

How” to solve the executive problem statement.

Power BI Desktop has an interactive interface with multiple sources of connectors. It helps to create interactive reports locally and the Power BI service is a cloud‑based service used to share content across the folks.

Let us talk about Data Cleaning

When the executive draws the visualization from the data which intervene from multiple sources with the regular growth of data, it is very much required to Clean -> Transform -> Model the data with aggregation and filter of data. Luckily, Power Query has an incredible number of features that are dedicated to helping you clean and prepare your data for analysis. You will learn how to simplify a complicated model, change data types, rename objects, and pivot data. You will also learn how to profile columns so that you know which columns have the valuable data that you are seeking for deeper analytics.

In other words, if the executive has to deliver data, he or she must begin the process of acquiring data from numerous sources, which is then followed by an investigative phase. It may contain data issues such as missing values, duplication, and inaccurate column names. As a result, executives must clean, transform, and model the data. Every month, the executive goes through the same procedure for new data. It’s become a very time-consuming chore.

“How” to address the executive problem statement

Power BI Desktop features an interactive interface with several connectivity sources. It is beneficial to build interactive reports locally, and the Power BI service is a cloud-based tool used to distribute content among users.

When the executive creates a visualization from data that comes from numerous sources and grows on a regular basis, it is critical to Clean -> Transform -> Model the data with data aggregation and filtering. Fortunately, Power Query includes a plethora of functions dedicated to assisting you in cleaning and preparing your data for analysis. You’ll discover how to simplify a complex model, switch data types, rename objects, and pivot data. You’ll also learn how to profile columns so you know which columns contain the critical data you need for further analytics.

Step 1: Transform the Data (Power Query in Action)

  • Open the Power Query Editor by selecting the Transform data option on the Home tab of Power BI Desktop to begin molding your data.

  • The data from your selected query appears in the center of the screen in the Power Query Editor, and the Queries pane on the left side presents the available queries (tables).

  • All actions you take to shape your data are documented when you operate with the Power Query Editor. The query then automatically applies your steps each time it connects to the data source, ensuring that your data is always shaped the way you specified. Power Query Editor only modifies a specific view of your data, so you can be certain that no changes are being made to your original data source. On the right side of the screen, in the Query Settings pane, you can see a list of your actions, as well as the query’s attributes.

  • The Power Query Editor ribbon includes several functions for selecting, viewing, and shaping data.

Step 2: Formatting the Data (Check on Column Headers & Names)

  • The first stage in molding your initial data is to identify the column headers and names within the data and then review their location to verify they are in the correct place. I used Excel as a data source and entered fake data.

Step 3: Import Excel Data

  • When we imported data from Excel, we discovered that it did not load correctly.

Step 4: Make the first row the header.

  • As a result, the data is difficult to read. Once you’ve determined the location of the column headings and names, you may make adjustments to restructure the data.

Step 5: Replace the Values

  • You may use the Replace Values functionality in the Power Query Editor to replace any value in a specified column with another value.

Power BI Data Transformation

After importing data into the Power Query Editor, the transformation processes can be executed in any sequence.

  1. Data Import- The first and most important step is to import the data from the source into Power BI. To import data, proceed as follows:

  • Navigate to the “Home” tab on the ribbon.

  • When you click “Get Data,” you will be given the opportunity to obtain the data from a different platform.

  • Select “Excel” because we have our unsorted data in Excel.

  1. Then it will open a window for you to choose the “Excel file” from your computer.

  2. The “Navigator” screen then asks you to pick the datasheets you want to work with; mark the sheets you want to work with.

  3. We will select the “Transform Data” option since we wish to sort our data.

  4. By selecting Load, the file is uploaded without being transformed.

36 views0 comments

Recent Posts

See All


Rated 0 out of 5 stars.
No ratings yet

Add a rating
bottom of page