TECH **Power Query Editor – Applied Steps & M Language Basics: Zero-Fluff Study Guide**

Power Query Editor – Applied Steps & M Language Basics: Zero-Fluff Study Guide

(For Power BI Engineers & PL-300 Exam Prep)

1. What This Is & Why It Matters

Power Query Editor is where 90% of Power BI data transformation happens—before your data even hits the model. Think of it like a data assembly line: - Raw materials (source data) go in (CSV, SQL, API, Excel, etc.).
- Applied Steps are the workstations that clean, reshape, and filter the data.
- M language is the blueprint that tells each workstation exactly what to do.

Why this matters in production:
- Broken transformations = broken reports. If you don’t understand Applied Steps, you’ll waste hours debugging why a column disappeared or why a merge failed.
- M is your escape hatch. When the UI can’t do what you need (e.g., custom logic, API pagination, or complex joins), M lets you write your own rules.
- Performance killer: Poorly structured M queries can slow down refreshes by 10x (or crash Power BI entirely). A single inefficient step can turn a 2-minute refresh into a 20-minute nightmare.
- Collaboration & maintenance: If you leave a query with 50 unnamed steps like "Changed Type1," "Filtered Rows2," etc., your teammates (or future you) will hate you. Clean, documented steps = scalable Power BI.

Real-world scenario:
You inherit a Power BI report with 12 data sources, 30+ Applied Steps, and no documentation. The refresh fails with a cryptic error: Expression.Error: The column 'CustomerID' of the table wasn't found. Where do you even start? This guide gives you the tools to diagnose, fix, and prevent this.

2. Core Concepts & Components

? Applied Steps

• Definition: A sequential list of transformations applied to your data in Power Query Editor. Each step is a record of an action (e.g., "Removed Columns," "Filtered Rows").
• Production insight: Every step adds overhead. Too many steps = slower refreshes. Combine steps where possible (e.g., filter + remove columns in one go).

? M Language (Power Query Formula Language)

• Definition: A functional, case-sensitive language used to write custom transformations in Power Query. Think of it like Excel formulas on steroids—but for data pipelines.
• Production insight: M is not SQL or DAX. It’s declarative (you describe what you want, not how to do it). Example: ```powerquery-m // M (Power Query) Table.SelectRows(Source, each [Status] = "Active")

// SQL equivalent SELECT * FROM Source WHERE Status = 'Active' ``` Key difference: M doesn’t optimize queries like SQL does. You must write efficient code.

? Step Dependencies

• Definition: Each Applied Step depends on the previous one. If you delete or modify a step, all subsequent steps may break.
• Production insight: Never rename a column in Step 5 if Step 10 references it. Power Query doesn’t auto-update references—you’ll get errors like The name 'OldColumnName' wasn't recognized.

? Query Folding

• Definition: The process where Power Query pushes transformations back to the source (e.g., SQL Server) instead of doing them in Power BI. Critical for performance.
• Production insight: If you disable folding (e.g., by using Table.Buffer() or unsupported M functions), Power BI pulls all data locally before transforming it. This can crash your report.

? Parameters & Functions

• Definition:
• Parameters: Variables you can reuse across queries (e.g., StartDate, API_Key).
• Functions: Reusable M code blocks (like a stored procedure in SQL).
• Production insight: Hardcoding values (e.g., Filter = "2023-01-01") is a maintenance nightmare. Use parameters for dynamic filtering.

? Error Handling

• Definition: M has no try-catch, but you can use try ... otherwise to handle errors gracefully.
  powerquery-m try [Column1] / [Column2] otherwise null
• Production insight: Never let errors fail silently. Log them (e.g., write to a "Errors" table) so you can debug later.

? Data Types & Nulls

• Definition: M is strict about data types. 123 (number) ≠ "123" (text). Nulls (null) are not the same as empty strings ("").
• Production insight: Type mismatches cause 80% of Power Query errors. Always explicitly set data types (e.g., Table.TransformColumnTypes).

? Query References

• Definition: One query can reference another (e.g., Sales = SalesRaw, SalesClean = Table.TransformColumns(Sales, ...)).
• Production insight: Avoid circular references (Query A → Query B → Query A). Power BI won’t warn you—it’ll just crash.

3. Step-by-Step Hands-On: Debugging & Optimizing a Broken Query

Prerequisites

• Power BI Desktop installed.
• A sample dataset (e.g., AdventureWorks Sales CSV).
• Scenario: You’re given a query that fails on refresh with: Expression.Error: The column 'ProductID' of the table wasn't found.

Step 1: Open Power Query Editor & Inspect Applied Steps

1. Open Power BI Desktop.
2. Get Data → Text/CSV → Load the AdventureWorks CSV.
3. Click Transform Data to open Power Query Editor.
4. In the Query Settings pane (right side), look at Applied Steps. You’ll see something like:
   ```
5. Source
6. Promoted Headers
7. Changed Type
8. Filtered Rows
9. Removed Columns
10. Renamed Columns
11. Merged Queries
    ```

Step 2: Identify the Broken Step

1. Click each step one by one, starting from the top.
2. At Renamed Columns, you see:
   powerquery-m
= Table.RenameColumns(#"Removed Columns",{{"ProductNumber", "ProductID"}})
   Problem: The step renames ProductNumber to ProductID, but the next step (Merged Queries) still references ProductNumber.
3. Fix: Either:
4. Update the merge step to use ProductID, or
5. Reorder steps so the merge happens before renaming.

Step 3: Rewrite the Merge Step (M Language)

1. Click the Merged Queries step.
2. In the formula bar, you see:
   powerquery-m
= Table.NestedJoin(#"Renamed Columns", {"ProductNumber"}, Products, {"ProductNumber"}, "Products", JoinKind.LeftOuter)
3. Fix: Change ProductNumber to ProductID:
   powerquery-m
= Table.NestedJoin(#"Renamed Columns", {"ProductID"}, Products, {"ProductNumber"}, "Products", JoinKind.LeftOuter)
4. Verify: The error disappears.

Step 4: Optimize the Query (Query Folding)

1. Right-click the Source step → View Native Query.
2. If you see SQL code, folding is working.
3. If you see let ... in, folding is broken.
4. Check for folding killers:
5. Table.Buffer() → Disables folding (use sparingly).
6. Table.Profile() → Disables folding (remove after debugging).
7. Custom functions → May disable folding (test with View Native Query).
8. Fix: If folding is broken, reorder steps to do filtering/aggregation before custom M code.

Step 5: Add Error Handling

1. Suppose you have a step that divides two columns:
   powerquery-m
= Table.AddColumn(#"Previous Step", "ProfitMargin", each [Profit] / [Revenue])
2. Problem: If [Revenue] = 0, this fails.
3. Fix: Add error handling:
   powerquery-m
= Table.AddColumn(#"Previous Step", "ProfitMargin", each try [Profit] / [Revenue] otherwise null)

Step 6: Document & Clean Up Steps

1. Rename steps to be descriptive:
2. ❌ Changed Type1 → ✅ Set Data Types
3. ❌ Filtered Rows → ✅ Filter Active Customers
4. Add comments (right-click step → Properties → Description):
   Filters out inactive customers (Status = "Inactive").
Applied before merging to reduce data volume.

4. ? Production-Ready Best Practices

? Security

• Never hardcode credentials in M queries. Use Power BI parameters or Azure Key Vault.
• Mask sensitive data (e.g., PII) in Applied Steps. Example: powerquery-m = Table.ReplaceValue(#"Previous Step", each [SSN], each Text.Repeat("*", Text.Length([SSN])), Replacer.ReplaceValue, {"SSN"})

⚡ Performance

• Minimize steps: Combine Filter Rows + Remove Columns into one step where possible.
• Avoid Table.Buffer() unless you know you need it (disables query folding).
• Use Table.SelectRows instead of Table.Filter for better readability.
• Push transformations to the source (e.g., filter in SQL, not Power Query).

? Reliability & Maintainability

• Name steps descriptively (e.g., Filter 2023 Sales instead of Filtered Rows1).
• Use parameters for dynamic values (e.g., StartDate, RegionFilter).
• Modularize with functions for reusable logic (e.g., fn_CleanCustomerName).
• Test with small datasets first before scaling to millions of rows.

? Observability

• Log errors to a separate table: powerquery-m = Table.AddColumn(#"Previous Step", "Error", each try [Calculation] otherwise "Error: " & Error.Record[Message])
• Monitor refresh times in Power BI Service. If a query takes >5 minutes, optimize it.
• Use Table.Profile() to check data quality (remove after debugging).

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus (PL-300)

Typical Question Patterns

1. Applied Steps Debugging

2. "A query fails with The column 'CustomerID' wasn't found. What’s the most likely cause?"

   
   
   • Answer: A previous step renamed or removed CustomerID.
   • Trap: They might show a step that adds CustomerID later—order matters!

3. M Language Syntax

4. "Which M function removes duplicate rows?"

   
   
   • Answer: Table.Distinct()
   • Trap: Table.RemoveDuplicates() (doesn’t exist).

5. Query Folding

6. "Which of these steps will not fold to SQL Server?"

   
   
   • Answer: Table.Buffer() or Table.Profile()
   • Trap: Table.SelectRows() does fold—don’t confuse it with Table.SelectColumns().

7. Error Handling

8. "How do you handle division by zero in M?"

   
   
   • Answer: try [Numerator] / [Denominator] otherwise null
   • Trap: if [Denominator] = 0 then null else [Numerator] / [Denominator] (works but is less idiomatic).

9. Parameters vs. Hardcoding

10. "You need to filter a query by a dynamic date. What’s the best approach?"
    • Answer: Use a parameter (Parameter_StartDate).
    • Trap: Hardcoding Filter = "2023-01-01" (breaks when date changes).

7. ? Hands-On Challenge (With Solution)

Challenge:

You have a table with a Date column. Write an M query that: 1. Adds a new column called IsWeekend (returns true if the date is Saturday/Sunday).
2. Handles nulls (returns null if Date is null).

Solution:

= Table.AddColumn(
    #"Previous Step",
    "IsWeekend",
    each if [Date] = null then null
         else if Date.DayOfWeek([Date], Day.Sunday) = 0 or Date.DayOfWeek([Date], Day.Sunday) = 6
         then true
         else false
)

Why it works:
- Date.DayOfWeek([Date], Day.Sunday) returns 0 for Sunday, 6 for Saturday.
- The if [Date] = null check prevents errors.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. Official M Language Reference – Bookmark this.
2. Power Query Best Practices (Microsoft) – Covers folding, performance, and security.
3. Guy in a Cube – Power Query Playlist – Short, practical videos.
4. Power BI Community – Power Query Forum – Real-world Q&A.

Final Pro Tip:

Power Query is a pipeline, not a one-time script.
- Test early, test often.
- Document every step.
- Assume the next person maintaining your query is a sleep-deprived version of you.

Now go break something (intentionally)—then fix it. That’s how you learn. ?