ExamFinch.com My Study

Study guide

3.1 Column Transformations and Data Types

Practice this exam

Microsoft Power BI Data Analyst PL-300

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the PL-300 Exam The Power BI Ecosystem

2 Chapter 1: Get or Connect to Data

1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters

3 Chapter 2: Profile and Clean the Data

2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection

4 Chapter 3: Transform and Load the Data

3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance

5 Chapter 4: Design and Implement a Data Model

4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables

6 Chapter 5: Create Model Calculations with DAX

5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference

7 Chapter 6: Optimize Model Performance

6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns

8 Chapter 7: Create Reports

7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers

9 Chapter 8: Enhance Reports for Usability and Storytelling

8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh

10 Chapter 9: Identify Patterns and Trends

9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns

11 Chapter 10: Create and Manage Workspaces and Assets

10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution

12 Chapter 11: Secure and Govern Power BI Items

11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle

3.1 Column Transformations and Data Types

Key Takeaways Explain This Topic AI

Split Column by delimiter, position, or pattern extracts subfields from combined columns.

Merge Columns concatenates multiple columns into one with an optional separator.

Add Column from Examples uses AI to infer transformation patterns from sample values you provide.

Extract operations pull specific parts from text (first/last characters, prefix, suffix, text between delimiters).

Column From Examples is one of the most powerful and commonly tested Power Query features.

Last updated: June 2026

Quick Answer: Power Query provides rich column transformation tools: Split Column breaks combined data apart, Merge Columns joins columns together, Extract pulls substrings, and Column From Examples uses AI to infer your desired transformation from sample outputs. These operations appear frequently on the PL-300 exam.

Split Column

Split Column separates a single column into multiple columns based on rules.

  • Split by Delimiter
  • The most common split operation: Select column → Transform tab → Split Column → By Delimiter

Options:

  • Setting Description
  • Delimiter Comma, semicolon, tab, space, custom character
  • Split at Left-most, Right-most, or Each occurrence
  • Advanced Split into rows instead of columns

Example: Split "John Smith" by space:

  • Column1: "John"
  • Column2: "Smith"

Example: Split "2026-03-31" by hyphen:

  • Column1: "2026"
  • Column2: "03"
  • Column3: "31"

Split by Number of Characters Split Column → By Number of Characters → Enter count → Left-most/Right-most/Repeating

Example: Extract first 3 characters of "US-2026-ABC":

  • Column1: "US-" (first 3 characters)
  • Column2: "2026-ABC" (remaining)
  • Split by Positions

Split at specific character positions — useful for fixed-width data: Split Column → By Positions → Enter positions (e.g., 2, 5, 8)

Merge Columns

Merge Columns concatenates two or more columns into a single column: Select multiple columns (Ctrl+Click) → Transform tab → Merge Columns

  • → Choose separator → Name the new column
  • Separators available: Space, Tab, Comma, Semicolon, Colon, Equals sign, Custom

Example: Merge FirstName and LastName with a space separator:

  • "John" + "Smith" → "John Smith"
  • M code: Table.CombineColumns(previousStep, {"FirstName", "LastName"},
  • Combiner.CombineTextByDelimiter(" "), "FullName")
  • Extract Operations

Extract pulls specific parts from text columns:

  • Operation Description Example Input Output
  • Length Character count "Power BI" 8
  • First Characters Left N characters "PL-300" (3) "PL-"
  • Last Characters Right N characters "PL-300" (3) "300"
  • Range Characters from position "PL-300" (pos 3, len 3) "300"
  • Text Before Delimiter Text before first occurrence " john@email.com " (@) "john"
  • Text After Delimiter Text after first occurrence " john@email.com " (@) "email.com"
  • Text Between Delimiters Text between two delimiters "[Important]" ([ and ]) "Important"
  • Column From Examples

Column From Examples is an AI-powered feature that infers transformations from sample values you type: Add Column → Column From Examples → From Selection or From All Columns

  • How It Works
  • Power BI shows a new blank column
  • You type the desired output for the first few rows
  • Power BI analyzes patterns and generates the M formula
  • Review and accept the generated transformation

Example Use Cases:

  • Extract domain from email: " user@microsoft.com " → "microsoft.com"
  • Format dates: "March 31, 2026" → "2026-03-31"
  • Create full names: "John" + "Smith" → "Smith, John"
  • Extract numbers from text: "Order #12345" → "12345"
  • Standardize phone numbers: "(555) 123-4567" → "5551234567"

Exam Tip: Column From Examples is heavily tested because it demonstrates practical data transformation skills. Know when to use it vs. manual transformation steps.

  • Add Custom Column
  • For transformations not covered by built-in operations: Add Column → Custom Column → Enter M formula
  • Example formulas: // Concatenation
  • [FirstName] & " " & [LastName]
  • // Conditional logic
  • if [Amount] > 1000 then "High" else "Low"
  • // Text manipulation
  • Text.Upper([Category])
  • // Date extraction
  • Date.Year([OrderDate])
  • Rename and Reorder Columns
  • Renaming
  • Double-click the column header and type the new name
  • Or: Right-click → Rename
  • Apply user-friendly naming conventions : "cust_first_nm" → "Customer First Name"
  • Reordering
  • Drag and drop column headers to rearrange
  • Or: Right-click → Move → Left/Right/To Beginning/To End

Best Practice: Rename columns to business-friendly names early in the query. This makes the model more intuitive for report builders and end users.

Choosing the Right Transformation

The exam often presents a single goal and asks for the best approach among several that would technically work. Use this decision guide:

  • Goal Preferred tool Why
  • Break one column into several (clean delimiter) Split Column by Delimiter Predictable, folds on databases
  • Pull a substring with an irregular pattern Column From Examples AI infers the rule from your samples
  • Combine columns with a fixed separator Merge Columns One step, no formula needed
  • Apply business logic or math Custom Column (M) Full control over the expression

Column From Examples is worth special attention because it appears so often. You type the desired output for a couple of rows (for example microsoft.com from user@microsoft.com ), and Power Query writes the M formula — typically using Text.AfterDelimiter , Text.BetweenDelimiters , or date-formatting functions. It is ideal when the pattern is obvious to a human but tedious to express manually.

Merge Columns vs. Merge Queries

A classic distractor pairs these two similarly named features that do completely different things:

Merge Columns — combines two or more columns in the same table into one (FirstName + LastName → FullName).

Merge Queries — joins two separate queries/tables on a key, like a SQL JOIN (covered in 3.3).

Exam Tip: If the scenario stays inside one table, it's Merge Columns ; if it brings data from another table, it's Merge Queries . Also remember that Split Column → Split into rows normalizes a multi-value cell ( "Finance,Sales" ) into separate rows rather than separate columns.

On the Exam

The PL-300 frequently tests:

  • Choosing between Split Column options (delimiter, position, characters)
  • Using Column From Examples for pattern-based transformations
  • Understanding Merge Columns vs. Merge Queries (different operations)
  • Applying naming conventions for user-friendly column names
  • Knowing when to use Custom Column vs. built-in transformations