Clean transform and load data in power bi

How to Clean Data in Power BI and Its Importance.

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.

2. Then it will open a window for you to choose the “Excel file” from your computer.
3. The “Navigator” screen then asks you to pick the datasheets you want to work with; mark the sheets you want to work with.
4. We will select the “Transform Data” option since we wish to sort our data.
5. By selecting Load, the file is uploaded without being transformed.