# Advanced Data Transformation Techniques

> Chapter 5 of the Courseiva PL-300 curriculum — https://courseiva.com/learn/pl-300/advanced-data-transformation

**Official objective:** 1.5 — Apply advanced transformations including M language basics, conditional columns, and custom functions

## Introduction

If you feed Power BI dirty data — like misspelt city names, inconsistent date formats, or null values scattered through your sales table — your reports will be wrong, your boss will lose trust in your dashboards, and you will fail the PL-300 exam. Advanced data transformation techniques are the tools that take raw, messy data and turn it into a perfect, analysis-ready dataset, and the PL-300 exam tests these techniques heavily because they are the foundation of every reliable Power BI report.

## The Recipe Book Analogy

In your grandmother's kitchen, her old recipe book is a mess.

Some recipes are written on napkins, others are in a scrawled notebook, and a few are just memories she keeps in her head. You want to make her famous Sunday roast, but the instructions are all over the place. One page says 'add a pinch of salt', another says 'season to taste', and the chicken cooking time is written in a margin so faint you can barely see it. This is exactly the mess of working with raw data.

Now, you decide to transform that kitchen chaos into a clear, typed-out recipe book. First, you standardise every ingredient name — 'large onion' becomes '1 large onion (approx 200g)'. You combine the scattered instructions from the napkin and the notebook into one single, clean paragraph. You convert the vague 'cook until done' into a precise 'roast at 180 degrees Celsius for exactly 45 minutes'. You even create a 'custom function' for the special gravy recipe so you never have to rewrite those six steps every time you make a roast. This act of cleaning, standardising, and combining is exactly what Power Query's 'Advanced Data Transformation' does. It takes a messy, unstructured mess of data — maybe from a CSV file, an Excel sheet, and a database — and applies a systematic, repeatable set of rules (the M language) to turn it into a clean, consistent, and reusable dataset. The result is one reliable cookbook, not a pile of confusing notes.

## Core explanation

Advanced data transformation in Power BI is done inside a tool called Power Query Editor. Think of this as the workshop where you clean, reshape, and combine your data before you ever build a single chart. The exam objective 1.5 focuses on three specific areas: M language basics, conditional columns, and custom functions.

**What is the M language?**

M stands for 'Mashup' language. It is the programming language that Power Query uses behind the scenes. Every time you click a button in the Power Query Editor — like 'Remove Columns' or 'Change Type' — Power Query writes a line of M code for you. You do not need to be a programmer to use it, but understanding the basics helps you debug errors and write more powerful transformations when the buttons are not enough.

An M expression always starts with 'let' and ends with 'in'. For example:

let
    Source = Excel.Workbook(File.Contents("C:\\Sales.xlsx"), null, true),
    Sales_Table = Source{[Item="SalesData",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sales_Table,{{"Date", type date}, {"Revenue", type number}})
in
    #"Changed Type"

This code loads an Excel file, picks the correct table, and changes the data types of two columns. You will see M code in the formula bar at the top of the Power Query Editor. The exam expects you to recognise basic M syntax, understand the 'let' and 'in' structure, and know that you can edit the code directly instead of using the ribbon buttons.

**Conditional Columns**

A conditional column is a column you create that says: 'If this condition is true, put value A; otherwise, put value B'. It is like adding a new rule to your dataset. For example, if you have a sales column and you want to create a column called 'Performance', you can write:

If Sales > 1000 then 'High'
Else if Sales > 500 then 'Medium'
Else 'Low'

This is done in Power Query Editor by going to Add Column > Conditional Column. The exam tests your ability to define the correct conditions and the order of those conditions — the first matching condition wins, so order matters.

**Custom Functions**

A custom function is a saved set of transformation steps that you can reuse on multiple columns or tables. Imagine you have 'CustomerName' and 'ProductName' columns, and you want to clean both by removing leading and trailing spaces and capitalising the first letter. Instead of repeating those steps twice, you create one function that does the cleaning, and then you apply it to both columns.

In Power Query, you create a custom function by writing M code that accepts an input value and returns a transformed value. You can also create a function from an existing query — right-click on a query and select 'Create Function'. The exam will test whether you understand what a function is, how to invoke it, and why it is more efficient than repeating the same transformation steps.

**Why these matter for your exam**

The PL-300 exam does not ask you to write complex M code from scratch. Instead, it tests your ability to recognise when to use these techniques, understand the steps the applied steps pane shows, and interpret what a given piece of M code does. You might get a question like: 'A user wants to create a column that categorises orders by value. Which transformation should they use?' The answer is 'Conditional column'. Another question might show you a piece of M code with an error and ask you to fix it — knowing the 'let...in' structure is critical.

## Real-world context

Let us walk through a typical day for Priya, a data analyst at a retail company.

Priya needs to build a monthly sales report for her manager. She receives three data sources:

- An Excel file from the store operations team containing daily transaction data.
- A CSV export from the online store showing web orders.
- A SQL Server database table with product details including stock levels and supplier info.

Each source has its own mess. The Excel file has dates formatted as 'dd/mm/yyyy' but the CSV uses 'mm/dd/yyyy'. The product names in the database use abbreviations like 'Lap-Dell-XPS' while the Excel file says 'Dell XPS Laptop'. Some rows in the CSV have empty 'CustomerName' values.

**Step one: Connect and preview**

Priya opens Power BI Desktop and uses Get Data to load all three sources into Power Query Editor. She looks at each table in the Applied Steps pane.

**Step two: Standardise dates**

She clicks on the 'Date' column in the Excel table and goes to Transform > Data Type > Date. Power Query writes the M code to change the type. She does the same for the CSV's date column, but notice that Power Query automatically guesses the correct region format based on the data preview. She double-checks the M code to confirm the conversion is correct.

**Step three: Clean product names**

She cannot use the simple 'Replace Values' button because the two product name formats are not identical. Instead, she creates a custom column using a conditional logic: if the product name contains 'Lap-Dell-XPS', she replaces it with 'Dell XPS Laptop'. She writes multiple conditions to map all common abbreviations.

**Step four: Handle missing customer names**

For the CSV rows with empty 'CustomerName', she creates a conditional column that says: if CustomerName is null, then use 'Unknown Customer', else keep the original value. This ensures no blank entries break her reports.

**Step five: Merge tables**

Now that the data is clean, Priya merges the three tables using Merge Queries. She joins the transaction table with the product details table on the 'ProductID' column. This is an advanced transformation that combines data from different sources into one unified table.

**Step six: Create a custom function**

Priya notices that both the product name column and the store name column need the same cleaning: remove leading/trailing spaces and capitalise the first letter of each word. Instead of repeating these steps twice, she creates a custom function called 'CleanText'. She then applies this function to both columns under Transform > Invoke Custom Function.

The final result is a single clean table with consistent dates, normalised product names, no empty customer names, and all useful columns merged together. Priya closes and applies the query, then builds her dashboard. The entire transformation process is recorded in M code, so next month, she only needs to refresh the data — Power Query runs all the steps automatically.

## Exam focus

The PL-300 exam tests your practical knowledge of Power Query transformations, not memorisation of M language syntax. Here is exactly what you need to know.

**Question types that appear**

- **Case study questions**: You get a scenario describing a data problem — like 'A sales table has dates stored as text and needs to be converted to proper date format' — and you need to choose the correct transformation step from a list of options.

- **Multiple-choice (single answer)**: These ask you to identify the best transformation technique for a specific goal. For example: 'Which transformation would you use to add a column that categorises profit into Low, Medium, or High?' Answer: Conditional Column.

- **Multiple-choice (multiple answers)**: You might see: 'Which of the following are valid ways to create a custom function in Power Query?' Choose all that apply. Common correct options include 'Create a parameter query' and 'Right-click a query and select Create Function'.

- **Drag-and-drop**: The exam may ask you to arrange steps in the correct order when using Merge Queries or Append Queries.

**Traps they set**

- **Trap: Confusing 'Conditional Column' with 'Calculated Column'**: A calculated column is created in the data model (after loading), while a conditional column is created in Power Query Editor (before loading). The exam loves to test this distinction. Remember: conditional column = Power Query; calculated column = DAX in the model.

- **Trap: Ignoring the order of conditions**: When you build a conditional column, the first condition that evaluates to true is applied. If you put 'High' first with 'Sales > 0', then all rows become 'High' because every number is greater than 0. The exam will give you a scenario with four conditions in an order that makes the result incorrect.

- **Trap: Forgetting that M code is case-sensitive**: In the exam, you might see a piece of M code that uses 'table' instead of 'Table' (with a capital T). This will cause an error. Knowing basic M syntax helps you spot these errors.

- **Trap: Assuming custom functions are only for multiple queries**: Custom functions can also be applied to multiple columns within the same query. This is a less obvious use case that the exam loves.

**Key concepts to memorise**

- The 'let...in' structure of M.
- The difference between 'Transform' (changes existing column) and 'Add Column' (creates new column).
- How to create a conditional column: Add Column > Conditional Column, then define the conditions.
- How to invoke a custom function: Transform > Invoke Custom Function.
- The purpose of the Applied Steps pane: it shows the sequence of transformations applied to your data.

**What you do not need to know**

You do not need to memorise the entire M language specification. You do not need to write M code from scratch. The exam focuses on recognition and application, not coding.

## Step by step

1. **Connect to your data source** — Open Power BI Desktop, click Get Data, and choose your source (Excel, CSV, SQL Server, etc.). This loads the raw data into Power Query Editor. The editor shows you a preview of the data before any transformations are applied.
2. **Open Power Query Editor** — Once data is loaded, the Power Query Editor window opens. On the left, you see a list of queries (tables). In the centre, you see the data preview. On the right, the Applied Steps pane lists all transformations you will perform.
3. **Create a Conditional Column** — Go to the Add Column tab, click Conditional Column. A dialog opens where you define a new column name and a set of rules. Each rule has three parts: a column name, an operator (like equals, greater than, contains), and a value. For example: If Revenue > 1000 then 'High'. The last rule is 'Otherwise' for everything else.
4. **Review the M code generated** — The formula bar above the data preview now shows the M code that Power Query wrote for your conditional column. You can see the 'Table.AddColumn' function and the nested 'if...then...else' statements. Editing this code directly lets you make precise changes that the dialog might not allow.
5. **Create and apply a Custom Function** — Duplicate a query that contains the steps you want to reuse. Right-click the query and choose 'Create Function'. Name your function (e.g., 'CleanText'). Then, on another column, go to Transform > Invoke Custom Function and select your function. This applies the same steps to that new column.
6. **Close and Apply the transformations** — Once all transformations are complete, go to the Home tab and click Close & Apply. This executes all the M code and loads the clean data into the Power BI data model. The Applied Steps pane is now permanently recorded — refreshing the report will re-run all steps.

## Comparisons

### Conditional Column vs Calculated Column (DAX)

**Conditional Column:**
- Created in Power Query Editor before data is loaded into the model
- Uses If-Then-Else logic with no DAX knowledge required
- Used for cleaning or categorising data as part of transformation

**Calculated Column (DAX):**
- Created in the data model using DAX formulas after data is loaded
- Uses DAX functions like IF, SWITCH, and CALCULATE
- Used for measures and advanced calculations that depend on the data model

### Custom Function vs Duplicate Query

**Custom Function:**
- A single definition of transformation steps that can be applied to multiple columns or queries
- Changes to the function automatically update all applications
- More efficient and less error-prone when the same logic is needed in multiple places

**Duplicate Query:**
- A new query that is a copy of the original query including all steps
- Each copy must be updated manually if the original logic changes
- Easy to create but leads to redundancy and maintenance challenges

### M Language (let...in) vs DAX Language

**M Language (let...in):**
- Used in Power Query Editor for data transformation and cleaning
- Code structure begins with 'let' and ends with 'in' to define variables and the final output
- Operates on tables and rows within the Power Query environment

**DAX Language:**
- Used in the data model for calculated columns, measures, and tables
- No 'let...in' structure; uses functions like SUM, CALCULATE, and FILTER
- Operates on the entire data model with relationships and filters applied

## Diagram

_The flow from raw data through Power Query Editor transformations (including conditional columns and custom functions) to clean data in the data model._

```mermaid
flowchart TD
    A[Raw Data Sources] --> B[Power Query Editor]
    B --> C[Apply Transformations]
    C --> D[Conditional Column?]
    D -->|Yes| E[Define If-Then-Else Rules]
    D -->|No| F[Custom Function?]
    F -->|Yes| G[Create Function from Steps]
    F -->|No| H[Standard Transformations]
    E --> I[M Code Generated]
    G --> I
    H --> I
    I --> J[Applied Steps Pane]
    J --> K[Close & Apply]
    K --> L[Clean Data in Model]
```

## Common misconceptions

- **Misconception:** M language is a completely separate programming language that I need to learn like Python or SQL. **Reality:** M is a functional language specific to Power Query, but most users never write raw M code. The exam tests your ability to read and understand basic M, not to write complex scripts from scratch. Power Query writes the code for you as you click buttons. (Beginners hear 'programming language' and panic. They assume they need a full coding background, but in reality, the exam only expects you to recognise common M patterns.)
- **Misconception:** A conditional column and a calculated column (DAX) are interchangeable; you can create either one in Power Query. **Reality:** A conditional column is created in Power Query Editor using the Add Column menu. A calculated column is created in the data model (after loading) using DAX formulas. They serve different purposes: conditional columns are for transforming data before loading; calculated columns are for calculations after loading. (The terms sound similar and both involve logic like IF-THEN-ELSE. Beginners assume they are the same thing, but the exam specifically distinguishes where each is created.)
- **Misconception:** Custom functions can only be used when you have multiple similar queries (like one for each month). **Reality:** Custom functions can be applied to multiple columns within the same query, not just multiple queries. For example, you can create a function to clean text and apply it to several text columns in a single table. (The phrase 'custom function' sounds advanced, so beginners think it has limited use. In reality, it is a flexible way to avoid repeating steps on different columns.)
- **Misconception:** The order of conditions in a conditional column does not matter because Power Query checks all of them. **Reality:** The first matching condition wins. If you put a broad condition (like Sales > 0) before a specific condition (like Sales > 1000), the specific condition will never be reached because all sales values are > 0. (This is a classic mistake because it mimics a common Excel IF function misunderstanding. The exam tests this exact trap.)
- **Misconception:** M language is only used when you manually type code; clicking buttons does not generate M code. **Reality:** Every button click in Power Query Editor generates M code in the background. You can see the code in the formula bar and the Applied Steps pane shows each transformation step. (Because the interface is graphical, beginners think no code is involved. They miss the fact that the exam might show them M code from a previous step and ask them to interpret it.)

## Key takeaways

- Power Query Editor records every transformation step in the M language, and you can see and edit the M code in the formula bar.
- A conditional column lets you add a column that assigns a value based on a set of rules using an 'If-Then-Else' logic.
- Custom functions allow you to package a series of transformation steps and reuse them on multiple columns or queries, saving time and reducing errors.
- The order of conditions in a conditional column matters — the first true condition is applied, so always list the most specific conditions first.
- Conditional columns are created in Power Query Editor before data is loaded into the data model, not after.
- You do not need to memorise all of M language syntax; the PL-300 exam focuses on reading and understanding basic M structures like 'let' and 'in'.
- Custom functions can be created by referencing a parameter query or by right-clicking an existing query and selecting 'Create Function'.
- The Applied Steps pane shows the history of every transformation you have performed, and you can reorder, delete, or rename steps in this pane.

## FAQ

**Do I need to learn M language to pass PL-300?**

No, you do not need to write M code from scratch. The exam tests your ability to read and understand basic M code — for example, recognising the 'let...in' structure and knowing that 'Table.AddColumn' adds a new column.

**What is the difference between a conditional column and a calculated column?**

A conditional column is created in Power Query Editor (before data is loaded) and uses If-Then-Else logic. A calculated column is created in the data model (after loading) and uses DAX formulas. The exam tests where each is built.

**How do I create a custom function in Power Query?**

You can duplicate a query that contains the steps you want to reuse, then right-click it and select 'Create Function'. Alternatively, you can create a parameter query and reference it from an M function.

**Why does my conditional column not work correctly?**

Most likely you placed a broad condition before a specific one. Remember that the first true condition wins. Always put the most specific conditions at the top.

**Can I use a custom function on multiple columns in the same table?**

Yes, that is one of the main benefits. After creating the function, go to Transform > Invoke Custom Function and select the columns you want to apply it to.

**What should I do if I mess up the M code in the formula bar?**

You can delete the step from the Applied Steps pane on the right. The data will revert to the state before that step. Alternatively, you can edit the M code directly to fix the error.

## Check your understanding

1. **In Power Query Editor, which option do you use to add a column that assigns a value based on a set of rules, such as categorising profit as 'High', 'Medium', or 'Low'?**

   Answer: The Conditional Column option under the Add Column tab.

2. **If you have an M code snippet that starts with 'let' and ends with 'in', what does the part between 'in' and the final semicolon represent?**

   Answer: The final expression that the query returns, which is usually the name of the last transformation step.

3. **True or False: A custom function in Power Query can only be used on multiple queries, not on multiple columns within the same query.**

   Answer: False. A custom function can be invoked on a column within a query using 'Transform > Invoke Custom Function'.

4. **What is the correct order of conditions in a conditional column to ensure that 'High' (>1000) gets applied before 'Low' (any other value)?**

   Answer: Place the condition for 'High' (Sales > 1000) first, then the condition for 'Low' as the 'Otherwise' clause.

5. **Where can you see the M code generated by each transformation step in Power Query Editor?**

   Answer: In the formula bar at the top of the Power Query Editor window, and also in the Applied Steps pane on the right side.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/pl-300/advanced-data-transformation