Column Transformations and Data Types

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:

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:

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

1. Power BI shows a new blank column
2. You type the desired output for the first few rows
3. Power BI analyzes patterns and generates the M formula
4. 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.

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