Transform Data with Power Query Editor — this is the single most hands-on skill you will learn for the PL-300 exam because real-world data almost never arrives in the shape you need. Whether you are merging sales records from two regions, splitting customer names into first and last, or pivoting a messy spreadsheet into clean rows, Power Query is the tool that takes raw, ugly data and turns it into analysis-ready tables without you writing a single line of code.
Jump to a section
A simple way to picture Transform Data with Power Query Editor
You start your Saturday morning with 6 raw ingredients: a whole chicken, a bag of carrots, a bunch of celery, 2 onions, a head of garlic, and a box of chicken stock. Your goal is to make a 4-serving pot of chicken noodle soup by dinner, but your kitchen is also your only counter space, and you have 3 hungry kids asking when lunch is.
Before you can cook, you need to transform those ingredients. You split the chicken into drumsticks, thighs, and breast pieces — like splitting a column of full names into First Name and Last Name. You peel and chop the carrots into half-inch rounds — that is applying a transformation to every piece in a column, like changing text case or rounding decimals. You crush the garlic with the flat of your knife because you need the flavour released fast — this is like merging two tables of data into one richer dataset. The celery you leave whole for the stock pot but slice for the final soup — that is caching a transformed query so you can reuse it in multiple later steps without processing the raw data again.
The Power Query Editor is your prep station: it does all the cutting, splitting, combining, and cleaning before the actual analysis begins. You never put dirty or misshapen vegetables straight into the pot — and a data analyst never loads unshaped data into a report. This kitchen analogy maps precisely because every transformation has a single, repeatable purpose: getting every ingredient ready so the cooking (the analysis) is smooth, fast, and yields exactly the result you planned.
Power Query Editor is a built-in tool inside Power BI Desktop that lets you change the structure and content of your data before you build any visualisations. Think of it as a dedicated workshop where every step you take is recorded and can be repeated automatically. This is fundamentally different from manually editing cells in Excel, where someone could accidentally delete a formula and break the whole spreadsheet. In Power Query, every action — every split, merge, rename, or filter — is saved as a 'step' in a list on the right side of the editor. You can go back to any step, change it, delete it, or reorder it, and the entire pipeline updates instantly.
The technical name for this process is 'data transformation'. It replaces the old workflow where analysts would spend hours manually cleaning spreadsheets with copy-paste, find-and-replace, and fragile Excel formulas. With Power Query, you can connect to a source file (like a CSV, a SQL Server database, or an Excel workbook), apply a series of transformations, and load the result into your data model. If the source data changes — for example, your sales team sends a new CSV each week — you just refresh the query, and Power Query runs through every step you defined to produce the same structure with the new data.
Let us walk through the four most common transformations that the PL-300 exam tests: merging, splitting, pivoting, and unpivoting.
Merging is when you combine two tables based on a matching column, similar to how a VLOOKUP works in Excel but much more powerful. For example, you have one table called Orders with a CustomerID column, and another table called Customers with CustomerID, Name, and Region. You merge them by matching CustomerID from the first table to CustomerID in the second. The result is a single table with all the order details plus the customer name and region. The key choice here is the 'join kind' — inner join (only matching rows), left outer join (all rows from the first table, matched where possible), full outer (all rows from both), and a few others. The exam loves to test which join kind to use in a given scenario.
Splitting is exactly what it sounds like: you take one column and divide it into two or more columns. A classic example is a Full Name column that contains 'John Smith'. You split at the space delimiter to get First Name as 'John' and Last Name as 'Smith'. You can split at a specific character (comma, space, dash), at a fixed number of characters (like splitting a postal code 90210 into '902' and '10'), or by position count. The split can happen into columns or into rows — if you have a column with comma-separated values like 'Red, Blue, Green', splitting into rows creates three rows, each with one colour.
Pivoting and unpivoting are inverse operations. Pivoting takes rows and turns them into columns. Imagine a sales table with Year, Region, and Sales Amount rows. If you pivot on the Region column and use Sales Amount as the values, you get one row per year, with separate columns for 'North America', 'Europe', and 'Asia', each containing the sales amount for that region and year. Unpivoting does the opposite: it takes multiple columns and turns them into attribute-value pairs. If you receive a report with columns for Q1, Q2, Q3, Q4, unpivoting transforms it into rows with Quarter and Sales columns, which is the format Power BI needs for most visualisations.
Power Query also offers 'Group By' for creating summary rows, 'Replace Values' for correcting data entry mistakes (like replacing 'N/A' with null), 'Remove Duplicates' to keep only unique rows, and 'Transpose' to swap rows and columns. Every transformation can be applied to a single column, multiple columns, or the entire table. The interface is visual — you click buttons and choose options — but behind the scenes, Power Query generates code in a language called 'M' (short for Power Query M formula language). You never need to write M to pass the PL-300 exam, but understanding that the steps are stored as M code helps you appreciate why Power Query is deterministic and repeatable.
The reason Power Query exists is that raw data is almost never analysis-ready. Databases might store dates as numbers, spreadsheets might have merged cells, and CSV files might use different date formats. Without transformation, your charts would be wrong, your totals would miscalculate, and your reports would be unreliable. Power Query gives you a structured, auditable way to fix all of that before your data ever reaches the visualisation layer. For the PL-300 exam, you need to know not just what each transformation does, but when to apply it, what the default settings are, and what common pitfalls to avoid.
Connect to your data source
Open Power BI Desktop, click 'Get Data' on the Home ribbon, and choose the source type (e.g., Excel Workbook, CSV, SQL Server). Select the file or database, then click 'Transform Data' instead of 'Load' to open Power Query Editor directly. This step is crucial because loading before transforming would bring raw, uncleaned data into your model.
Promote headers and set data types
In Power Query Editor, use the 'Use First Row as Headers' button on the Home tab if your table has column names in the first row. Then, for each column, click the data type icon next to the column header (e.g., ABC for text, 123 for number, calendar for date) to lock in the correct type. This prevents issues like numbers being treated as text in calculations.
Apply transformations to clean and shape the data
Use the Transform tab to perform operations like Split Column, Replace Values, or Pivot/Unpivot. For example, select a column with full names and use Split Column by Delimiter (space) to create First Name and Last Name columns. Each transformation adds a step to the Applied Steps panel on the right, which you can rename by right-clicking for clarity.
Merge or append queries if combining multiple sources
On the Home tab, click 'Merge Queries' to combine columns from another table using a matching key, such as matching Customer ID from a sales table to a customer lookup table. Alternatively, use 'Append Queries' to stack rows from two tables with identical columns, like monthly sales reports. Choose the correct join kind (e.g., left outer) based on your business requirement.
Remove unnecessary columns and filter rows
In Power Query Editor, select columns you do not need and press Delete on your keyboard, or right-click and choose 'Remove Columns'. Use the filter dropdown on any column to exclude rows that are not relevant, such as filtering out test records or rows with null values in critical columns. This reduces the data size and improves performance.
Load the transformed data into Power BI
When all transformations are complete, click 'Close & Apply' on the Home tab. This runs all your steps and loads the final table into the Power BI data model. You can now use the data in visualisations, and the Applied Steps remain intact so you can double-click the query in the Fields pane to reopen Power Query Editor for future adjustments.
As a Power BI Data Analyst, your typical Monday morning might start with an email from the Sales Director containing a CSV export of the previous quarter's deals. The file has 14 columns, but only 6 are relevant for the report. One column, 'Opportunity Owner', contains full names like 'Jordan Lee, Sr.' and 'Maria Gómez-Garcia'. Another column, 'Close Date', is formatted as '2024-03-15' in the CSV but your corporate database uses '15/03/2024'. And there is a 'Product List' column with multiple products per row separated by semicolons, like 'Widget A; Widget B; Service Plan C'.
Here is what you actually do in Power Query Editor: first, you connect to the CSV file. Power Query previews the data, and you immediately see the column types are all 'Any' — meaning Power BI has guessed but not locked them in. You select the 'Opportunity Owner' column and use the 'Split Column by Delimiter' feature, choosing space as the delimiter. This creates two columns: 'Opportunity Owner.1' and 'Opportunity Owner.2'. But you notice that 'Maria Gómez-Garcia' split into three parts because of the hyphen. You undo that step and instead use 'Split Column by Character Transition' — a smarter way that splits between lowercase and uppercase letters, or between letters and non-letters, preserving the hyphen as part of the surname. You rename the resulting columns to 'First Name' and 'Last Name'.
Next, you handle the 'Product List' column. Since each row can have multiple products, splitting into rows is the correct move. You select the column, go to Split Column, and choose 'By Delimiter' with semicolon, but critically, you choose 'Split into Rows' instead of 'Split into Columns'. This creates one row per product, meaning a single deal with three products becomes three rows, each containing the full deal information plus one product. Now each row represents one product sale, making it possible to chart product-level data.
The 'Close Date' column needs to be standardised. You select the column and in the Transform tab, use 'Date' -> 'Parse Date'. Power Query recognises the YYYY-MM-DD format and converts the data type to Date. Then you use 'Replace Values' to fix any anomalies — for example, some rows have 'N/A' where the deal is still open. You replace 'N/A' with 'null' (the blank indicator in Power BI).
Finally, you merge this sales CSV with a lookup table from the company's SQL database that contains Region and Manager for each salesperson. You go to Home -> Merge Queries, select the two tables, and choose 'Sales Person Name' as the matching column from the CSV and 'Employee Name' from the SQL table. You select a Left Outer join so that every sales row appears, even if the salesperson is not found in the lookup table. The result replaces the salesperson name with a structured column containing Region and Manager, which you then expand by clicking the expand icon in the column header. After all transformations, you have a clean, analysis-ready table that you load into your data model. Every week when a new CSV arrives, you just refresh the query, and the same seven transformation steps run automatically — no manual cleaning needed.
The PL-300 exam tests 'Transform Data with Power Query Editor' in the context of knowing which button to click and what option to choose for a given data problem. The exam is not a Power Query simulation; instead, it presents multiple-choice questions that describe a data shape problem and ask you to select the correct transformation or sequence of steps. You need to be able to distinguish between similar-sounding operations.
Here are the specific concepts the exam loves to test:
Split Column: They will ask whether to split 'Into Columns' or 'Into Rows'. The trap is that beginners often default to splitting into columns even when the data should logically become multiple rows. For example, a column containing 'Red; Blue; Green' should be split into rows if each colour is a separate fact record. The exam question will describe a scenario with a one-to-many relationship and expect you to choose Split into Rows.
Merge Queries versus Append Queries: Merging combines columns from two tables based on a key. Append stacks rows from two tables with the same columns. A frequent exam trap describes a scenario where you have sales data from January and February in two separate Excel sheets with identical columns. The correct action is Append, not Merge. Another trap is that they describe 'combining tables without a common key' — beginners might still try Merge, but Append is the answer when you are just stacking rows.
Join kinds in Merge: The exam will present a business requirement like 'show all customers, even if they have not placed an order' and ask which join to use. The answer is left outer join on the Customers table. They may also test the difference between inner join (only matching rows) and full outer join (all rows from both tables). Remember that the 'left' table is the first table you select in the merge dialog.
Pivot versus Unpivot: This is a high-frequency topic. The exam will show a table with months as columns (Jan, Feb, Mar) and ask you to make it analysis-ready. The correct transformation is to select those month columns and use Unpivot Columns. The trap is that some beginners think they need to Pivot, but pivoting creates columns from rows — the opposite of what is needed here. Unpivot turns wide data into tall data, which is the format Power BI prefers for most visualisations.
Data type detection and transformation: The exam tests your ability to recognise when a column is stored as text but should be a number or date. A question might show a column where numbers have leading zeros (like '00123') and ask why the data type is not automatically detected. The answer is that leading zeros are preserved only if the column is treated as text; converting to number drops the zeros.
Group By: The exam expects you to know that Group By creates summary rows. A question might ask how to create a table of total sales per region — the answer is to Group By the Region column and aggregate the Sales column with Sum.
Common exam traps include:
'Remove Duplicates' versus 'Group By with Count': Remove Duplicates deletes rows that are exact duplicates across all selected columns. Group By with Count shows you how many duplicates exist without deleting them. A question might ask 'which method to use to find how many customers ordered multiple times' — the answer is Group By, not Remove Duplicates.
'Replace Values' versus 'Replace Errors': Replace Values works on any value you specify. Replace Errors only works on error values (cells shown as 'Error' in the preview). Beginners confuse them and replace a text string with Replace Errors, which does nothing.
The difference between 'From File' and 'From Database' connections: The exam asks which connector to use for a specific source type. Remember that 'From File' includes CSV, Excel, Text, and XML. 'From Database' includes SQL Server, Oracle, and IBM Db2.
To prepare, practise identifying the correct transformation for a given data shape. The exam expects you to know the buttons and their locations in the Power Query Editor ribbon. For example, 'Split Column' is on the Home tab and the Transform tab under the Split Column dropdown. 'Merge Queries' is on the Home tab. 'Group By' is on the Transform tab. Memorise these locations because the exam sometimes asks where a specific feature is found.
Every transformation you apply in Power Query Editor is automatically saved as a step that can be reordered, edited, or deleted without affecting the original data source.
Split a column into rows instead of columns when each cell contains multiple values that each represent a separate record or fact.
Use Merge Queries to combine columns from two tables based on a matching key, and Append Queries to stack rows from two tables with the same structure.
Unpivot columns to transform wide data (multiple columns representing the same type of data) into tall, analysis-friendly rows.
The join kind determines which rows appear after a merge: left outer keeps all rows from the first table, inner keeps only matches, and full outer keeps all rows from both tables.
Group By summarises data by creating aggregated rows (e.g., sum, average, count) for each group, while Remove Duplicates deletes rows that are exact copies across selected columns.
These come up on the exam all the time. Here's how to tell them apart.
Split Column into Columns
Keeps the same number of rows in the output
Creates new columns from the split values
Best when each row's data should expand horizontally, like splitting full name into first and last name
Split Column into Rows
Increases the number of rows in the output
Creates new rows instead of new columns
Best when each cell contains a list of values that each need their own record, like splitting a product list into individual products
Merge Queries
Combines columns from two tables based on a matching key
Similar to a VLOOKUP or SQL JOIN
Requires a related column in both tables to match on
Append Queries
Stacks rows from two tables with the same columns
Similar to copy-pasting rows from one table below another
Does not require a matching key; tables just need same column structure
Pivot Columns
Turns unique row values into new column names
Reduces the number of rows and increases columns
Often used to create a crosstab or summary table
Unpivot Columns
Turns multiple columns into attribute-value pair rows
Increases the number of rows and reduces columns
Essential for making wide reports analysis-ready by creating tall data
Remove Duplicates
Deletes entire rows that are exact copies across selected columns
Does not create a summary; modifies the original table
Only keeps the first occurrence of each duplicate row
Group By
Creates a new summary table with one row per group and aggregated values
Does not modify the original table; produces a separate table
Useful for counting duplicates or summing values per group
Left Outer Join
Returns all rows from the left table, plus matching rows from the right table
Non-matching rows from the right table show as null
Use when you want to keep all records from the first table even without a match
Inner Join
Returns only rows that have a match in both tables
Excludes any rows that do not have a matching key in both tables
Use when you only need records that exist in both data sources
Mistake
Splitting a column always creates more columns, never more rows.
Correct
Splitting a column can create more rows when you choose the 'Split into Rows' option. For example, splitting a column of comma-separated values into rows gives you one row per value, not one row per original row with extra columns.
In Excel, splitting by delimiter always creates new columns, so beginners naturally assume Power Query behaves the same way. The extra option to split into rows is a Power Query feature that does not exist in Excel, so it is easily overlooked.
Mistake
Merging queries and appending queries are the same thing because both combine data.
Correct
Merging combines columns from two tables based on a matching key (like a VLOOKUP). Appending stacks rows from two tables that have the same columns. They serve completely different purposes and are found under different buttons in Power Query.
The words 'merge' and 'append' sound similar and both describe combining data. In everyday language, people say 'merge two spreadsheets' even when they mean 'append the monthly sheets together'. The exam uses the strict Power Query definitions, so this common language confusion leads to wrong answers.
Mistake
Pivot and Unpivot are reversible operations, so you can always use one to undo the other.
Correct
Pivot and Unpivot are not always perfectly reversible. If you pivot a table and then unpivot the result, you may get a different number of rows or columns because pivoting can summarise data (using aggregation) or lose information if there are duplicate value combinations.
The mathematical names 'pivot' and 'unpivot' sound like inverses, similar to addition and subtraction. Beginners assume doing one after the other returns to the original state, but in practice, pivot often requires an aggregation (e.g., sum, count) that discards individual row details.
Mistake
You should replace null values with 0 before loading data into Power BI to avoid blank visualisations.
Correct
Replacing null with 0 can distort calculations like averages and counts because 0 is a real value while null means 'no data'. Power BI handles nulls gracefully in most visualisations, and many best practices recommend treating null as missing rather than forcing a zero.
In Excel, blank cells cause errors in formulas, so people habitually fill blanks with 0. This habit carries over to Power Query, but Power BI's data model treats missing values differently, and filling in 0 can produce misleading business metrics.
Mistake
Power Query Editor can only transform data that comes from Excel or CSV files.
Correct
Power Query Editor can connect to and transform data from dozens of sources: SQL Server, Oracle, SharePoint, web APIs, Microsoft Entra ID audit logs, Azure Blob Storage, and many more. File-based sources are just one category.
Many PL-300 candidates come from an Excel background and only use Power Query for local files in their day jobs. The exam covers multiple source types, and forgetting the breadth of connectors is a common oversight.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
In the Applied Steps panel on the right side of Power Query Editor, click the 'X' next to any step to delete it. You can also right-click a step and choose 'Delete Until End' to remove that step and all steps after it. There is no standard undo button, but you can always delete steps.
'Split Column into Columns' divides a single column into multiple columns, keeping the same number of rows. 'Split Column into Rows' creates additional rows, one for each value in the original cell, which is useful when a cell contains a list of values that each need their own row.
Use Merge Queries when you need to combine two Power Query tables based on a matching column, similar to a VLOOKUP in Excel. Merge Queries is more flexible because it can handle multiple matching columns and different join kinds (inner, left outer, full outer) without complex formulas.
The 'Group By' button on the Transform tab summarises rows based on one or more grouping columns and creates an aggregated row for each group. For example, you can group by 'Region' and calculate the sum of 'Sales' to get total sales per region in a new table.
Click on the 'Error' value to see the error message, which usually indicates a data type mismatch or invalid data. You can use 'Replace Errors' on the Transform tab to replace all error values with a specific value like null, or fix the underlying transformation (e.g., ensure the data type matches before performing calculations).
Yes. If you save your Power BI file, the queries and their steps remain. When you get a new file with the same structure, you can go to 'Transform Data', click 'Data source settings', and change the file path. Then refresh the query, and Power Query applies all the same steps to the new data.
You've finished Transform Data with Power Query Editor. Continue through the PL-300 study guide to build a complete picture of the exam.
Done with this chapter?