Imagine you've just received a sales report from your company's CRM system. It's a CSV file — technically usable, but a mess. The column headers have inconsistent capitalization. There's a "Full Name" column when you need separate first and last name fields. Some revenue figures have dollar signs baked into the text, so Excel treats them as words instead of numbers. Your boss wants this in a dashboard by end of day.
This is the exact situation Power Query was built for. Power Query is the data preparation engine built into Power BI (and Excel). It lets you connect to raw data sources, clean and reshape the data, and load it into your model — all without writing a single line of SQL or Python. And here's the beautiful part: every transformation you apply gets recorded as a step, so the next time you receive an updated version of that file, you just hit refresh and all your cleaning happens automatically.
By the end of this lesson, you'll be able to import a CSV file into Power Query, navigate the Power Query Editor interface, apply a series of practical transformations to make your data analysis-ready, and load the clean result into Power BI. You'll understand not just what to click, but why each step matters.
What you'll learn:
Before touching a button, let's build some intuition.
Think of Power Query as a highly organized kitchen prep cook. Raw ingredients (your data) come in from various suppliers (CSV files, databases, web APIs). The prep cook follows a recipe — a series of precise steps — to chop, season, and arrange everything before it goes to the chef. The recipe is written down, so every morning the same prep work happens identically without anyone having to remember what they did yesterday.
That "recipe" in Power Query terms is called the query. Every transformation you apply gets added as a named step in the Applied Steps pane. If your source file updates next week with new rows, you refresh the query and Power Query re-executes every step from top to bottom in under a second. You never redo manual work.
This is the fundamental shift Power Query creates: instead of cleaning data by hand each time, you build a repeatable, auditable process.
Let's work with a realistic scenario. You're a data analyst at a regional retail company. Your operations team exports a monthly sales transaction file from their system. Download or create a CSV file called sales_transactions.csv with the following content — you can paste this into a plain text file and save it with the .csv extension:
transaction_id,full_name,sale_date,product_category,revenue,region,status
1001,john smith,2024-01-03,$1200.00,Electronics,North,Completed
1002,SARAH JOHNSON,2024-01-04,$850.50,Apparel,South,Completed
1003,Mike Davis,2024-01-05,$340.00,Electronics,East,Pending
1004,emily clark,2024-01-07,,Home Goods,West,Completed
1005,TOM NGUYEN,2024-01-08,$2100.75,Electronics,North,Cancelled
1006,Laura Perez,2024-01-09,$975.00,Apparel,East,Completed
1007,james wilson,2024-01-10,$430.00,,South,Completed
1008,ANNA LEE,2024-01-11,$620.00,Home Goods,West,Pending
1009,Chris Martin,2024-01-12,$1890.50,Electronics,North,Completed
1010,debra harris,2024-01-15,$310.00,Apparel,South,Completed
Notice the problems deliberately baked into this file:
full_name has inconsistent capitalization (all lowercase, all uppercase, title case)revenue has dollar signs, making them text strings rather than numbersrevenue valueproduct_category valuestatus includes "Cancelled" transactions you may want to filter out for a revenue reportThese are exactly the kinds of issues you encounter in real-world data every week.
Launch Power BI Desktop. When it opens, you'll see a splash screen with options. Close that dialog to get to the blank report canvas.
To import your file, navigate to the top ribbon and click Home → Get Data → Text/CSV. A file browser window will open. Navigate to wherever you saved sales_transactions.csv and click Open.
Power BI will show you a preview dialog. This is not Power Query yet — it's just a quick look at the raw file. You should see your data in a table-like preview with ten rows and seven columns. Power BI has already detected that the first row contains column headers, which is correct.
At the bottom of this preview dialog, you'll see two buttons: Load and Transform Data.
Critical distinction: Clicking Load would import the data as-is, dollar signs, capitalization issues, and all. Clicking Transform Data opens the Power Query Editor, where you can clean things up first. Always choose Transform Data when you know the data needs work — which is most of the time.
Click Transform Data.
The Power Query Editor opens in a new window. It can look overwhelming at first, so let's break it into four key areas:
The Formula Bar runs across the top, just below the ribbon. It shows the M formula (Power Query's underlying language) for whatever step is currently selected. You don't need to write M code as a beginner, but it's good to know it's there — every click you make generates M code behind the scenes.
The Queries Pane on the left lists all the queries in your file. Right now you have one: sales_transactions. As your projects grow, you might have dozens of queries here.
The Data Preview in the center is the main work area. It shows a sample of your data as it currently looks, updating after each transformation so you can see the result immediately.
The Applied Steps Pane on the right side is arguably the most important panel in Power Query. This is where every transformation you apply gets recorded as a named step. Right now you should see two steps already listed: Source (the connection to your file) and Promoted Headers (Power Query automatically detected that your first row was headers and promoted them). Power Query did that work for you automatically.
Look at the column headers in the data preview. You'll notice small icons to the left of each column name. These icons indicate the data type Power Query has assigned to each column — whether it thinks the data is text, a number, a date, and so on.
Data types matter enormously. If Power Query thinks your revenue column is text, you can't sum it, average it, or create any numeric calculation with it. Getting types right is foundational.
You should see that revenue has been assigned a text type (indicated by an "ABC" icon) because of those dollar signs. sale_date may have been assigned a date type already, but let's verify everything.
To change the revenue column type, first click the column header revenue to select it. Then, in the ribbon, go to Transform → Data Type → Decimal Number.
Nothing will happen yet — or rather, something important will happen: Power Query will add a Changed Type step to the Applied Steps pane, and the data preview will update. But wait — the revenue column still won't look right because the dollar signs are still in the values. We'll fix that shortly. The type change alone isn't enough.
Why decimal number instead of whole number? Revenue figures like $850.50 have cents — decimal places. Whole Number would truncate those. Always match the data type to the actual precision of your data.
While we're here, verify the other columns:
transaction_id: should be Whole Numbersale_date: should be Datefull_name, product_category, region, status: should all be TextClick each column's type icon directly (the icon to the left of the column name) to change it without going through the ribbon. A dropdown appears with all available types. This is often faster.
The dollar sign problem is extremely common when data comes from financial systems or spreadsheets. The value $1200.00 is stored as the text string "dollar-sign-one-two-zero-zero-dot-zero-zero." We need to strip that dollar sign so we're left with a pure number.
Select the revenue column by clicking its header. Then go to Transform → Replace Values. A dialog box appears with two fields: Value To Find and Replace With.
In Value To Find, type $ (just the dollar sign character). Leave Replace With completely empty. Click OK.
Power Query will strip the dollar sign from every value in that column. Now the values look like 1200.00, 850.50, and so on. A new step called Replaced Value appears in your Applied Steps pane.
Now go back and change the data type of revenue to Decimal Number again. (The previous type change may have already been applied, but Power Query sometimes needs you to reconfirm after a value replacement.) The column header icon should now show "1.2" indicating a decimal number type.
Tip: The order of steps matters. If you had tried to change the data type to Decimal Number before replacing the dollar signs, Power Query would have returned errors for every row because "dollar-sign-one-two-zero-zero" can't be converted to a number. Always clean the values first, then assign the type.
Your stakeholders want to be able to filter, sort, or group by last name. Right now you have a single full_name column. Let's split it into first_name and last_name.
Click the full_name column header to select it. Then go to Transform → Split Column → By Delimiter.
A dialog appears asking how you want to split the column. Power Query may have already detected the space character as the most likely delimiter (the character that separates the values). If it hasn't, type a single space in the delimiter field. Under Split at, select Left-most delimiter — this tells Power Query to split on the first space it finds, which handles names like "john smith" correctly.
Click OK. Power Query splits the column into two new columns: full_name.1 and full_name.2. Those names aren't great, so let's rename them.
Double-click on the full_name.1 column header. The header becomes editable. Type first_name and press Enter. Repeat for full_name.2, renaming it to last_name.
Two new steps appear in Applied Steps: Split Column by Delimiter and Renamed Columns.
Edge case to know: Names with three parts (like "Mary Jo Smith" or "Dr. James Wilson") would behave unexpectedly here — the third part would stay with the last name. In production, you'd add additional logic, but for this beginner walkthrough, our dataset keeps things simple.
The first_name and last_name columns still have inconsistent capitalization — some are all lowercase, some are all uppercase. This looks unprofessional in a report and causes grouping issues (Power BI treats "john" and "John" as different values).
Select the first_name column. Hold Ctrl and also click the last_name column header so both are selected simultaneously.
Go to Transform → Format → Capitalize Each Word. Power Query applies "proper case" formatting, so "john" becomes "John," "SARAH" becomes "Sarah," and so on.
A step called Capitalized Each Word appears in Applied Steps. Both name columns now show clean, consistent Title Case.
Your revenue report should only show completed and pending transactions. Cancelled orders shouldn't inflate your analysis, so let's filter them out.
Click the small dropdown arrow on the right side of the status column header. A filter menu appears showing all unique values in that column: Cancelled, Completed, Pending.
Uncheck Cancelled and leave Completed and Pending checked. Click OK.
The row where Tom Nguyen had a Cancelled transaction (row 1005 in our original data) disappears from the preview. A step called Filtered Rows is added to Applied Steps.
This is non-destructive. You didn't delete that row from your source file. You told Power Query's recipe: "when you run, skip rows where status is Cancelled." If that policy ever changes, you can click the
Filtered Rowsstep in Applied Steps and delete it, or modify its filter, and the data comes back instantly.
Look at your data preview. Row 1004 (Emily Clark) has a blank revenue cell, and row 1007 (James Wilson) has a blank product_category cell. Missing values are a decision point — there's no universal right answer. Do you replace them with zeros? Remove the row? Leave them as null?
For revenue, a blank almost certainly means the data wasn't captured — including it as zero would misrepresent your numbers. For a sales report, it's reasonable to remove rows where revenue is missing.
Click the dropdown arrow on the revenue column header. Scroll down to Remove Empty or look for the checkbox option next to null. Uncheck null and click OK.
Emily Clark's row is now excluded.
For product_category, a null category might still be a valid sale. Rather than removing the row, let's replace the null with the text "Unknown" so the row stays in the analysis but the gap is clearly flagged.
Select the product_category column. Go to Transform → Replace Values. In Value To Find, type null (lowercase). In Replace With, type Unknown. Click OK.
James Wilson's category now shows "Unknown" rather than a blank, and his row is preserved.
Take a moment to look at your Applied Steps pane. You should see a list of steps something like this:
This is the complete "recipe" for your data transformation. Click on any step to see what the data looked like at that point in the process. Click Source and you'll see the raw, uncleaned data. Click the last step and you see the final clean version. This is Power Query's superpower — full transparency and editability of every decision you made.
When you're satisfied with your transformations, click Home → Close & Apply in the Power Query Editor ribbon. This executes every step in your query, applies them to the full dataset (not just the preview sample), and loads the result into the Power BI data model.
The Power Query Editor closes and you return to the Power BI report canvas. In the Fields pane on the right side of the screen, you'll see your sales_transactions table with all your clean, properly typed columns listed underneath it.
Try clicking the revenue column in the Fields pane and dropping it onto the canvas. Power BI should immediately be able to sum it and display a card visual. This works because revenue is now a proper decimal number, not text strings with dollar signs.
Use the skills from this lesson to extend the transformation. After loading your data, go back into Power Query by clicking Transform Data in the Home ribbon.
Challenge 1: Add a new column called revenue_band that categorizes transactions. In Power Query, go to Add Column → Conditional Column. Set up logic so that if revenue is greater than 1000, the value is "High," if it's greater than 500, the value is "Medium," and otherwise the value is "Low." Name the column revenue_band.
Challenge 2: Rename the transaction_id column to Transaction ID (with spaces and proper capitalization) to make it more report-friendly.
Challenge 3: Sort your data by sale_date in ascending order using Home → Sort Ascending while the sale_date column is selected.
When finished, click Close & Apply and verify your new revenue_band column appears in the Fields pane with the correct categories.
"My data type change created errors in the column." This almost always means there are values in the column that can't be converted to the target type. The most common cause is extra characters like dollar signs, commas in numbers (like "1,200"), or percentage signs. Use Replace Values to remove these characters before changing the data type.
"I accidentally deleted a step I needed." Click Edit → Undo (or Ctrl+Z) immediately. If you've already done other steps since, click on the step just before the deleted one in Applied Steps and re-apply the transformation. Steps can also be reordered by dragging them in the Applied Steps pane, with caution — some steps depend on previous ones.
"Splitting the name column created three columns instead of two." This happens when a name contains more than one space (like a middle name or a double-barreled last name). Go back to the Split Column step in Applied Steps, click the gear icon next to it, and change the split setting to Left-most delimiter rather than Each occurrence of the delimiter.
"My filter removed rows I wanted to keep." Click the Filtered Rows step in Applied Steps. A gear icon appears next to the step name — click it to reopen the filter dialog and adjust your selections.
"Power Query shows 'Expression.Error' in some cells." This is often a type mismatch. Click the step where errors first appear (by clicking through Applied Steps one at a time) and look for what changed. Errors typically appear as yellow warning cells in the preview — you can click one to see the error message, which usually tells you exactly what went wrong.
Let's recap what you accomplished in this lesson. You connected Power BI to a CSV file using Get Data, opened the Power Query Editor, and applied seven practical transformations: replacing characters, changing data types, splitting a column, standardizing text formatting, filtering rows, and handling null values. You also saw how the Applied Steps pane records every decision as a reusable, editable recipe.
More importantly, you understood why each transformation matters — not just how to click the buttons. Data types determine what calculations you can run. Applied Steps make your process repeatable. Non-destructive filtering means you can always revisit decisions without losing data.
Where to go from here:
Power Query is one of those tools that compounds over time. Each transformation you learn multiplies in value because you can combine it with everything else you know. You've just built a solid foundation — now go find a messy dataset and get to work.
Learning Path: Getting Started with Power BI