How to Perform ETL in Power BI

You can’t build a great dashboard with messy data. Before you can create insightful charts and KPIs, you have to gather, clean, and shape your raw data into something usable. This process, known as ETL, is the foundation of any reliable report, and Power BI has a surprisingly powerful, built-in tool to handle it. This guide breaks down how to perform the entire ETL - Extract, Transform, and Load - process for your reports right inside of Power BI using its best-kept secret: the Power Query Editor.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is ETL, Really?

ETL stands for Extract, Transform, and Load. It’s a three-step process data analysts use to move data from various sources into a single, clean database or, in this case, a reporting tool like Power BI. Think of it like cooking: you gather your raw ingredients (Extract), then you wash, chop, and season them (Transform), and finally, you put them in the pot to cook your meal (Load).

• Extract: This is the "get data" step. You're pulling raw data from its source, whether it's an Excel spreadsheet, a folder of CSV files, a SQL database, or a web page.
• Transform: This is where the magic happens. You clean up the extracted data. This can include deleting unnecessary columns, fixing typos, changing data types (like making sure dates are treated as dates, not text), splitting columns, and combining different data tables.
• Load: This is the final step where your clean, transformed data is loaded into its destination - in our case, the Power BI data model - so you can start building visualizations.

Skipping or rushing the ETL process is why dashboards break and numbers don't add up. Garbage in, garbage out. By mastering ETL within Power BI, you ensure the insights you deliver are accurate and trustworthy.

Meet Your ETL Hub: The Power Query Editor

Power BI’s tool for handling all things ETL is the Power Query Editor. It’s a graphical interface that lets you perform complex data transformations without writing a single line of code. Every change you make is recorded as a step, creating a repeatable recipe that automatically cleans your data every time you refresh your report. This saves you from the mind-numbing task of manually cleaning the same report every single week.

Now, let's walk through each stage of the process.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 1: Extracting Your Data

First, you need to connect Power BI to your data sources. Power BI can connect to hundreds of different sources, but the process is largely the same for all of them.

Connecting to a Data Source

For this example, let's imagine we're connecting to a messy Excel file containing monthly sales data.

1. Open Power BI Desktop.
2. On the Home ribbon, click Get Data. You’ll see a list of common data sources. If you don't see yours, click More... to open the full list.
3. Select your source. We'll choose Excel Workbook and navigate to our sales file.
4. The Navigator window will appear, showing you the different tables and sheets within the file. Select the sheet you want to work with. You'll see a preview of the data on the right.
5. Here comes the most important choice: at the bottom, you have two options, Load and Transform Data. Always choose Transform Data. Clicking Load dumps the raw, messy data directly into your model, while Transform Data opens the Power Query Editor, where you can clean it up first.

Once you click "Transform Data," the Power Query Editor will open in a new window, and you're ready to start shaping your data.

Step 2: Transforming Your Data in Power Query

The Power Query Editor is where you'll spend most of your time. On the right side, you'll see a pane called Applied Steps. This is your personal undo button and a living log of every transformation you apply. Let’s go through some of the most common and useful transformations.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Cleaning and Tidying Up

Remove Unnecessary Columns and Rows

Most data exports come with extra columns you don’t need. To remove them, simply select the column header (hold Ctrl to select multiple) and then right-click and choose Remove Columns. You can also find this option in the Home ribbon.

Similarly, you can remove blank or irrelevant rows. The Remove Rows option in the ribbon lets you remove top rows, bottom rows, duplicates, or rows with errors.

Change Data Types

Power Query is pretty good at guessing data types, but it's not perfect. A column of dates might be imported as text, or numerical product IDs might be labeled as whole numbers, which you can't sum correctly. To fix this, click the icon next to the column header (like "ABC" for text or "123" for numbers) and select the correct data type (e.g., Date, Whole Number, Text).

Handle Missing or Null Values

Blank cells (or "nulls") can cause havoc in your calculations and charts. You can deal with them in a couple of ways:

• Replace Values: Right-click on a column header and select Replace Values. You can find "null" and replace it with something more meaningful, like "0" for sales data or "N/A" for text fields.
• Filter them out: Click the filter dropdown on a column header and uncheck "(Null)" to remove those rows from your dataset entirely.

Shaping and Restructuring

Splitting Columns

A classic data-cleaning task is splitting a single column into multiple. For example, you may have a "Customer Name" column with "John Smith" that you want to split into "First Name" and "Last Name".

1. Select the column you want to split.
2. In the Transform or Add Column tab, click Split Column.
3. Choose your method. "By Delimiter" is the most common. In our "John Smith" example, the delimiter is a space. Power Query will automatically detect it.
4. Click OK, and your single column is now two separate columns.

Merging and Appending Queries

If you're pulling data from multiple places, you'll eventually need to combine them. Power Query gives you two primary ways to do this:

• Merging Queries (Joining): Think of this as a super-powered VLOOKUP. Merging lets you combine two tables side-by-side based on a common column. For example, you could merge a Sales table with a Product Details table using "Product ID" as the common key to bring in the Product Category for your sales data.
• Appending Queries (Stacking): This is for stacking tables on top of each other. It's useful when you have data in the same format but in different files (e.g., Q1 Sales, Q2 Sales, Q3 Sales). Appending combines them into one master table for the full year.

Both options can be found in the Home ribbon within the Power Query Editor.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 3: Loading Your Transformed Data

Once you are happy with how your data looks in the Power Query Editor, it’s time to send it to the Power BI data model. This is the easiest step of all.

In the top-left corner of the Power Query Editor, click the Close & Apply button. Power Query will close, applying all your transformation steps to the full dataset and loading the clean, structured data into Power BI. You'll now see your prepared tables in the "Fields" pane on the right side of the main Power BI window, ready for building reports and dashboards.

Best Practices for ETL in Power BI

As you get more comfortable, keep these tips in mind to work more efficiently:

• Filter and Slim Down Early: The fewer rows and columns Power Query has to process, the faster your report will refresh. If you know you only need data from a specific department or the current year, filter that out as one of your first steps.
• Document Your Steps: The "Applied Steps" pane is a great start, but you can also right-click on any step and rename it to something more descriptive (e.g., change "Filtered Rows" to "Removed Test Accounts"). This helps you (and your teammates) understand the logic later on.
• Don't Be Afraid to Use "Add Column": The "Transform" tab modifies your existing columns, while the "Add Column" tab creates new ones. Often, it's safer to create a new, transformed column than it is to alter the original one, as this makes it easier to troubleshoot if something goes wrong.

Final Thoughts

Mastering the ETL process within Power BI's Power Query Editor transforms it from a simple visualization tool into a robust business intelligence environment. By dedicating time to properly extracting, transforming, and loading your data, you can build reliable, automated reports that give you crystal-clear insights without the weekly grind of manual clean-up.

While Power Query is a fantastic tool, it still operates on a very manual, click-by-click basis that takes time to learn and execute perfectly. We built Graphed because we believe getting insights shouldn't require you to first become a data prep expert. Our approach is to automate the entire ETL. We connect directly to your marketing and sales platforms, handle the data pipelines for you, and give you an AI-powered semantic layer that understands your data from day one. Instead of spending hours in an editor, you can just ask in plain English, "What was my sales conversion rate by campaign last month?" and get an instant, real-time dashboard without any of the manual wrangling.