Power Query Basics: Transform & Clean Data

Hello! Welcome to the next step in your Power BI journey.

In our last lesson, we focused on getting data into Power BI. We explored how to connect to various sources like Excel files and web pages and saw the Navigator window, which gives you two choices: Load or Transform Data. We've been holding off on that second option, but today, we're diving in.

This lesson is all about the Power Query Editor, the engine room of Power BI. Our goal is to learn how to use it to perform basic data transformation and cleaning tasks. You'll often hear the phrase "Garbage In, Garbage Out" in data analysis. This lesson is about making sure you start with clean, well-structured data, so the financial visualizations you create are accurate and trustworthy.

1. Welcome to the Power Query Editor

When you click "Transform Data" after connecting to a data source, you don't load the data into the main Power BI report view. Instead, you open a separate window: the Power Query Editor. Think of it as a data preparation workshop where you can shape, clean, and standardize your data before it's ready for analysis.

This dedicated environment is incredibly powerful because the changes you make are non-destructive and repeatable. Let's get familiar with its layout.

The "Applied Steps" Pane: Your Safety Net

The most important concept to grasp in Power Query is the Applied Steps pane. Every single action you take—removing a column, changing a data type, replacing a value—is recorded as a step in a list.

This is a massive advantage over cleaning data in Excel, where undoing a change made ten steps ago is often impossible. Here, you have a complete, editable history of your work.

2. Essential Data Cleaning and Transformation Tasks

Now, let's get hands-on. We'll walk through the most common transformations you'll need when working with financial data. The best way to learn these is by seeing them in action.

A. Checking and Changing Data Types

Power BI does a good job of guessing data types, but it's not always perfect. A column of stock prices read as Text instead of Decimal Number will cause errors in your charts and calculations. This is always the first thing you should check.

You can identify a column's data type by the icon in its header (e.g., ABC for text, 123 for whole number, 1.2 for decimal, a calendar for date).

B. Structuring and Reshaping Your Data

Financial datasets often need some structural work before they are useful. This can involve removing irrelevant information or combining/splitting columns to get them into the right format.

1. Removing Unnecessary Columns
Your source data might contain columns you don't need for your analysis (e.g., internal notes, irrelevant IDs). Removing them makes your data model cleaner and more efficient.

2. Splitting and Merging Columns
This is a very common requirement.

• Splitting: You might have a column like "City, Country" that you want to split into two separate columns for mapping.
• Merging: You might get separate "Year", "Month", and "Day" columns that you need to combine into a single Date column for time-series analysis.

C. Cleaning Data at the Cell Level

Once the structure is right, you need to look at the values themselves. Typos, extra characters, blank values, and duplicates can all invalidate your analysis.

1. Replacing Values and Removing Blank Rows
Imagine a stock price column where some values are entered as "N/A" or are simply blank. You can't perform calculations on this. You need to either replace these with a number (like 0) or remove the rows entirely.

2. Removing Duplicates
If you have a table of company information, you want to ensure each company appears only once. The "Remove Duplicates" feature is perfect for this.

Test your understanding!

You import a CSV file of daily stock data for Reliance Industries Ltd. (RELIANCE.NS) and notice a few problems in the Power Query Editor:

1. The Date column is formatted as text (ABC).
2. The Volume column, representing shares traded, is correct, but the Adj Close price column is also formatted as text because some values have a rupee symbol (₹) in front of them.
3. There is a Notes column that is completely empty and you don't need it.

What three basic transformation steps would you perform in order to clean this data for analysis?

Show answer

1. Change Data Type: Select the Date column and change its data type from Text to Date.
2. Replace Values & Change Type: Select the Adj Close column. First, use Replace Values to find the ₹ symbol and replace it with nothing (leave the 'Replace With' box empty). Then, change the column's data type to Decimal Number.
3. Remove Columns: Right-click the header of the Notes column and select Remove.

3. Applying Your Work

Once you're satisfied with your data transformations, you need to load the clean data into your Power BI model. You do this by clicking the Close & Apply button on the Home ribbon of the Power Query Editor.

This closes the editor and applies all the steps you defined to the data as it's loaded into the main Power BI window. The best part? These steps are saved with your report. The next time you refresh your data, Power BI will automatically perform all those cleaning steps for you.

Conclusion

Fantastic work! You've just learned one of the most critical and powerful features of Power BI. Mastering the Power Query Editor is the key to creating reliable and accurate financial reports. Clean data is the bedrock of trustworthy analysis.

Key Takeaways:

• The Power Query Editor is a dedicated environment for cleaning, shaping, and transforming your data before it's loaded for visualization.
• Every action is recorded in the Applied Steps pane, creating a repeatable and editable recipe for your data cleaning process.
• Fundamental transformations include:
  • Changing data types (e.g., text to date, text to currency).
  • Removing unnecessary columns.
  • Splitting or merging columns to get the right structure.
  • Replacing incorrect values and handling blanks.
  • Removing duplicates to create clean lists.
• Clicking Close & Apply loads your cleaned data into the model and saves your transformation steps for future refreshes.

In this lesson and the last, we've learned how to import data from multiple sources and clean each table individually. But what if you have a table of stock transactions and a separate table with company details? How do you link them? In our next lesson, we will explore the relationship model in Power BI and learn how to create simple relationships between your data tables, unlocking much deeper analytical possibilities.