How to Transform Excel Data in Power BI

Trying to visualize a messy Excel file in Power BI is a classic headache. Your spreadsheet might be perfect for human eyes - with merged cells, colorful headers, and helpful notes - but those very features can cause Power BI to stumble. This guide will walk you through exactly how to bridge that gap by transforming your raw Excel data directly inside Power BI, turning chaotic spreadsheets into clean, report-ready information.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Transforming Data is a Non-Negotiable First Step

You can't build a strong house on a shaky foundation, and you can't build an insightful dashboard with messy data. The concept of "garbage in, garbage out" is brutally true in data analysis. When you import an Excel file as-is, Power BI might misinterpret it, leading to broken visuals, incorrect calculations, and hours of frustration trying to figure out why your sales-per-month chart isn't working.

Common issues in source Excel files include:

• Extra Rows: Title rows, empty rows for spacing, or footer rows with notes like "Report generated on..."
• Merged Cells: Often used for spanning headers across multiple columns.
• Incorrect Data Types: Dates formatted as text, numbers with currency symbols that are read as text, or postal codes treated as numbers (and losing their leading zeros).
• "Wide" Data: Information spread across columns, like having a separate column for each month's sales, instead of one column for "Month" and one for "Sales Value."

Cleaning these up one time within Power BI’s Power Query Editor means that every time you refresh your report with a new version of that Excel file, all your cleaning steps will be applied automatically. You clean it once, and it stays clean forever.

Getting Started: Your Guide to Connecting and Transforming

The magic happens in an environment called the Power Query Editor. Think of it as the workshop where you prepare your raw materials (your data) before you build your project (your dashboard). Don't worry, any changes you make here won't affect your original Excel file, they only exist within your Power BI report.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Connecting Your Excel File

First, we need to bring your data into Power BI. It's a straightforward process, but with one crucial fork in the road.

1. Open a blank Power BI Desktop report.
2. From the Home ribbon, click on Get Data and select Excel workbook.
3. Navigate to your Excel file and click Open.
4. A Navigator window will pop up, showing you all the sheets and data tables within your workbook. Select the sheet you want to import.
5. Here’s the important part: Instead of clicking "Load," click "Transform Data" at the bottom right. This is your ticket into the Power Query Editor.

Clicking "Transform Data" opens a new window, the Power Query Editor, with a preview of your spreadsheet data. Now, the real work begins.

The Most Common Excel Data Transformations in Power BI

Once you're in the Power Query Editor, you have a powerful set of tools at your fingertips. Let's tackle some of the most frequent cleanup tasks you'll encounter with Excel data.

1. Removing Unwanted Rows and Columns

Most spreadsheets designed by humans have extra stuff - titles at the top, totals at the bottom, or empty columns for spacing. Let’s get rid of them.

• To Remove Top Rows: If your report has a few rows of titles or descriptions at the top, go to the Home tab in the Power Query ribbon, click Remove Rows, and select Remove Top Rows. Enter the number of rows to delete.
• To Remove Blank Rows: Similarly, you can find Remove Blank Rows in the same dropdown menu. This cleans up any empty spacer rows in your dataset.
• To Remove a Column: If you have columns you don’t need, simply right-click the column header and select Remove. You can also select multiple columns by holding down Ctrl and then right-clicking to remove them all at once.

2. Promoting the First Row to Headers

It's incredibly common for Excel files to have the column names in the first row. By default, Power BI might import them as generic "Column1," "Column2," etc. This is an easy fix in Power Query.

On the Home tab, there's a big, easy-to-find button that says "Use First Row as Headers." Click it once, and Power Query will promote that first row to become the official headers for your columns. It's often the very first or second step you'll take.

Pro Tip: Sometimes, a messy Excel file might apply the wrong header promotion. The reverse action, "Use Headers as First Row," can be found in the same dropdown menu if you need to undo it.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

3. Changing Column Data Types

Power BI tries its best to guess the data type for each column (Text, Whole Number, Decimal Number, Date, etc.), but it can often get it wrong, especially if there are mixed values in an Excel column.

You can see the current data type represented by a small icon in the column header (e.g., "ABC" for text, "123" for a whole number, a calendar for a date). If a column with numerical values has an "ABC" icon, you won't be able to perform mathematical calculations on it.

To fix this, simply click the icon on the column header and select the correct data type from the list. It's crucial to set appropriate types for:

• Numbers: So you can sum, average, and calculate.
• Dates: So you can analyze trends over time (e.g., this month vs last month).
• Geography: Set text fields like "Country," "State," or "City" to their proper data category for map visuals.

4. Unpivoting Columns: The Game Changer

This is arguably the most powerful transformation for people coming from Excel. Excel users often create "wide" data tables where each time period or category gets its own column. For example, a sales report might have columns like this: Product | Jan Sales | Feb Sales | Mar Sales | ...

This format is great for a static view but terrible for dynamic reporting. Power BI prefers "long" (or tall) data, which looks like this:

This "long" format allows you to easily slice, dice, and filter by Month in your reports. Getting there from the "wide" format is easy with "Unpivot."

1. Select the column(s) that you want to keep as they are. In our example, you would select the Product column.
2. Go to the Transform tab in the ribbon.
3. Click the dropdown on Unpivot Columns and select Unpivot Other Columns.

Magically, Power Query will transform all the other columns (your months) into two new columns: "Attribute" (which will contain the month names) and "Value" (which will contain the sales figures). You can then rename these new columns to "Month" and "Sales" for clarity.

5. Cleaning and Splitting Text Columns

Text data is rarely perfectly clean. It might have unwanted spaces, inconsistent casing, or multiple pieces of information packed into one column.

• Trim & Clean: Right-click a text column header, go to Transform, and you’ll see options like Lowercase, UPPERCASE, Capitalize Each Word, Trim (removes whitespace from the beginning and end), and Clean (removes non-printable characters).
• Split Column: Say you have a "Full Name" column that you want to split into "First Name" and "Last Name." Right-click the header, choose Split Column, and select By Delimiter. You can choose a common delimiter like a space, comma, or even a custom character. Power BI will automatically create the new columns for you.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

The Automation Secret: Applied Steps

As you click buttons and perform these transformations, you'll notice a list growing on the right-hand side of the Power Query Editor under a pane called Applied Steps.

This pane is the secret sauce of Power BI’s data automation. Each action you take - removing a row, promoting headers, unpivoting data - is recorded as a step in this list. You can think of it as a repeatable recipe.

When you get a new version of your Excel file next week or next month and hit "Refresh" in Power BI, it will re-run every single one of those applied steps in order. A process that used to take you 30 minutes of manual VLOOKUPs and cell formatting every Monday morning is now fully automated and takes seconds. This is how you reclaim your time and build truly scalable reports.

Final Thoughts

Mastering the Power Query Editor is the single most valuable skill for dramatically improving your efficiency and the quality of your analytics in Power BI. By learning to clean and reshape your Excel data just once, you create a robust, automated workflow that turns messy spreadsheets into a reliable foundation for crystal-clear reports and dashboards.

While skills in Power BI are incredibly useful, we know that the real challenge often lies in connecting all the dots - when your data lives not just in Excel but also in Google Analytics, Shopify, Facebook Ads, and a dozen other platforms. We built Graphed to solve this very problem by simplifying the entire process. Just connect your data sources, and then ask questions or describe the dashboards you want in simple, natural language - Graphed builds everything for you in real-time, helping you get from raw data to actionable insights in seconds, not hours.