How to Transform Data in Power BI

Building an insightful dashboard in Power BI starts long before you drag your first chart onto the canvas. It begins with clean, well-structured data, and that’s where data transformation comes in. This article will walk you through the essential steps and techniques for transforming your raw data into a reliable foundation for your reports using the Power Query Editor.

What Exactly is Data Transformation?

Think of data transformation like preparing your ingredients before you start cooking. You wouldn't just throw unwashed, uncut vegetables into a pot and expect a great meal. Similarly, raw data from sources like spreadsheets, databases, or SaaS apps is rarely ready for immediate analysis. It often contains errors and inconsistencies and needs to be reshaped to be useful.

Data transformation is the process of cleaning, structuring, and enriching that raw data to make it suitable for analysis and visualization. In Power BI, this critical step happens inside a powerful tool called the Power Query Editor. Getting this part right is fundamental because the quality of your reports and dashboards depends entirely on the quality of the data they're built on. Garbage in, garbage out.

Meet the Power Query Editor: Your Data Transformation Workspace

The Power Query Editor is a data connection and preparation engine integrated into Power BI. It's where you'll perform all the cleaning and shaping tasks. When you connect to a data source in Power BI Desktop, you're given two options: "Load" or "Transform Data."

• Load: This pushes the data straight into your Power BI data model as-is.

• Transform Data: This is your gateway to the Power Query Editor, allowing you to reshape the data before it's loaded.

You should almost always choose "Transform Data." Once you open the editor, you'll see a few key areas:

• The Ribbon: Just like other Microsoft products, the ribbon at the top contains tabs (Home, Transform, Add Column, View) with various transformation commands.

• Queries Pane: On the left, this lists all the data tables (queries) you're currently working with.

• Data Preview: The main area in the center shows you a sample of your data, allowing you to see the real-time effects of your transformations.

• Applied Steps: This might be the most important part. On the right, every change you make is recorded as a sequential "step." This makes your entire process repeatable, editable, and non-destructive.

Common Data Transformation Tasks in Power BI

Let's walk through some of the most frequent and useful data transformations you'll perform in the Power Query Editor. We'll use a sample dataset of sales transactions for our examples.

This sample data has all sorts of issues we need to fix: inconsistent naming, extra spaces, incorrect data types, and combined information.

1. Cleaning Up Columns and Rows

The first step is often to get rid of what you don't need and fix simple structural issues.

Removing Unwanted Columns

Our Email column has missing values and isn't needed for our sales analysis. To remove it:

1. Select the column(s) you want to remove by clicking on the header. (Hold Ctrl to select multiple.)

2. In the Home tab of the ribbon, click on Remove Columns.

This action removes clutter and can improve your report's performance by reducing the size of your data model.

Filtering Out Irrelevant or Blank Rows

You can also remove rows based on certain criteria. For instance, if you want to remove orders with no Unit Price:

1. Click the dropdown arrow on the Unit Price column header.

2. Uncheck (null) from the list of values or use the "Remove Empty" filter.

3. Click OK.

The entire row associated with that blank cell will be removed from your query.

2. Changing Data Types

Correct data types are essential. If Power BI thinks a number column is text, you can't perform calculations. If it views a date column as text, you can't analyze trends over time.

In our sample, the order-date is text, and Quantity might be imported incorrectly. To fix this:

1. Look for the icon next to the column name in the header (e.g., "ABC" for text, "123" for whole number).

2. Click the icon.

3. Select the correct data type from the dropdown menu. In our case, we'd change order-date to Date and ensure Unit Price is Decimal Number.

Power BI is smart and will try to guess the data type on import, but always double-check!

3. Splitting Columns

Splitting is perfect for situations where one column contains multiple pieces of information. Our Product Info column contains the product code, type, color, and size, all separated by hyphens.

To split this into separate, more useful columns:

1. Select the Product Info column.

2. Go to the Home tab and click Split Column. Choose By Delimiter.

3. Power Query will likely detect the hyphen as the delimiter automatically. If not, you can specify it.

4. Choose to split at "Each occurrence of the delimiter."

5. Click OK.

You will now have four new columns that you can rename to something meaningful like "Product Code," "Category," "Color," and "Size." Don't forget to rename them by double-clicking the column headers!

4. Replacing Values

Inconsistent data entry is a common problem. In our Country/Region column, we have "United States" and "USA." To analyze by country, these need to be standardized.

1. Select the Country/Region column.

2. Go to the Transform tab and click Replace Values.

3. In the dialog that appears:

   • For Value To Find, type USA.

   • For Replace With, type United States.

4. Click OK.

You can use this same functionality to fix capitalization inconsistencies or correct common misspellings.

5. Cleaning and Formatting Text

Our Customer Name column has extra spaces ("Jane Smithy") and the Country/Region has trailing spaces ("Canada "). Power Query makes this easy to fix.

1. Select the column(s) you want to clean.

2. Go to the Transform tab.

3. Click on Format. You'll see a dropdown with several options:

   • Trim: Removes any leading or trailing whitespace. This is a must-use for almost all text fields.

   • Clean: Removes any unprintable characters.

   • Uppercase / Lowercase / Capitalize Each Word: This standardizes the text casing. Let's use Capitalize Each Word for the names.

Applying both Trim and Capitalize Each Word to the customer name and country columns will create perfectly clean, standardized text fields.

6. Adding a Custom Column with a Formula

You can create new columns based on calculations from existing ones. Let's calculate the Total Sales for each order line item (Unit Price * Quantity).

1. Navigate to the Add Column tab.

2. Click on Custom Column.

3. In the new window:

   • Give your new column a name, e.g., "Total Sales".

   • Enter the formula in the box. You can type it or double-click the column names on the right to insert them. The formula would be: [Unit Price] * [Quantity].

4. Click OK.

A new Total Sales column will appear at the end of your table. Make sure to set its data type to Decimal Number!

7. Conditionally Adding Columns

Sometimes you need to create a new column based on a set of rules, or business logic. For example, let's categorize sales into "Small Order" or "Large Order" based on the Quantity.

1. Go to the Add Column tab and click on Conditional Column.

2. Set up your logic in the window:

   • New column name: Order Size

   • If Quantity is greater than 10 then Large Order.

   • Else: Small Order.

3. Click OK.

This is a simple example, but you can add multiple ELSE IF clauses for more complex scenarios.

8. Unpivoting Columns (a game-changer)

Sometimes data is structured in a "wide" format, which can be difficult to analyze. For example, imagine if sales were laid out like this:

This is hard to filter or visualize on a timeline. The ideal format is "tall" or "long." Unpivoting transforms it.

1. Select the columns you want to keep as they are (in this case, the Product column).

2. Go to the Transform tab.

3. Click the dropdown on Unpivot Columns and select Unpivot Other Columns.

This will transform your data into a much more usable structure:

You can then rename the "Attribute" and "Value" columns to "Month" and "Sales." This structure is far superior for creating reports in Power BI.

The Power of "Applied Steps"

As you've performed these transformations, a list has been growing in the "Applied Steps" pane on the right. This is an editable history of everything you've done. You can click on any previous step to see what the data looked like at that stage. If you made a mistake, you can simply click the 'x' next to a step to delete it, or click the gear icon to modify its settings.

Once you are happy with all your transformations, go to the Home tab and click Close & Apply. This loads your clean, transformed data into your Power BI model, ready for creating relationships and building visualizations.

Final Thoughts

Learning how to transform data in the Power Query Editor is a fundamental Power BI skill. By following these steps to clean, shape, and standardize your data, you are creating a trustworthy dataset that will lead to accurate, meaningful, and reliable reports that people can use to make better decisions.

While mastering tools like Power BI is a valuable skill, the process for marketers and sales teams can feel like a detour from their actual jobs. Manually applying these transformational steps takes time and practice. At Graphed, we automate the hard parts. We connect directly to your marketing and sales data sources - like Google Analytics, Shopify, and Salesforce - and handle the complex data preparation behind the scenes. This allows you to go from raw data to building dashboards instantly using plain English, skipping the manual clicks and cleaning so you can get answers faster than ever.