Splitting and Merging Columns

Real-world data constantly arrives with values crammed into a single column that should be separate, or values split across multiple columns that should be combined. A full name in one field, a city and postcode jammed together, a date and time sharing a column, a product code that encodes both category and SKU. This lesson covers every technique for splitting columns apart and merging them back together — including the shortcut that writes the transformation for you.

The Working Dataset

All examples in this lesson use the same source table — a typical export that packs too much into individual columns.

Split Column — All Methods

Split Column is accessed from the Transform tab (replaces the original column) or the Add Column tab (creates new columns alongside the original). Always use Add Column when you want to keep the source value intact. The split dialog offers seven different splitting strategies.

Example 1 — Split FullName into FirstName and LastName

The FullName column contains first and last name separated by a single space. Split by delimiter (space), at each occurrence, produces two new columns.

Example 2 — Extract Category from ProductCode

The ProductCode column stores values like "ELEC-1042" where the prefix before the dash is the product category. Two approaches work here — Split by Delimiter (dash), or Extract Text Before Delimiter. Use Extract when you only need one part and want to leave the original column untouched.

Option A — Split Column by Delimiter (dash)
  Select ProductCode column
  Transform tab → Split Column → By Delimiter
  Delimiter: Custom → enter: -
  Split at: Left-most delimiter only
  Result: ProductCode.1 (category) and ProductCode.2 (SKU number)
  Rename ProductCode.1 → Category
  Rename ProductCode.2 → SKU

Option B — Extract (keeps original column intact)
  Select ProductCode column
  Add Column tab → Extract → Text Before Delimiter
  Delimiter: -
  Result: a NEW column "Text Before Delimiter" alongside ProductCode
  Rename → Category
  Original ProductCode column is unchanged

M code for Option B:
  = Table.AddColumn(
      #"Changed Type",
      "Category",
      each Text.BeforeDelimiter([ProductCode], "-"),
      type text
    )

Example 3 — Split OrderDateTime into Date and Time

The OrderDateTime column combines a date and time in a single value — "2024-01-05 09:42:00". For most reports you need these as separate columns: a Date column to connect to a Calendar table, and a Time column if time-of-day analysis is needed.

Extract — Getting Part of a Text Value

The Extract menu (Add Column tab → Extract or Transform tab → Extract) provides targeted text extraction without a full split. Use it when you need only one piece of a value and want to avoid creating unnecessary columns.

Merging Columns

Merging combines two or more columns into a single column, joining the values with a separator you define. Common uses include rebuilding a full address from parts, creating a composite key for relationships, or combining a year and month number into a sortable period label.

Merging with Custom Column — More Control

For merges that need conditional logic, different separators between different column pairs, or mixed text and numbers, use Add Column → Custom Column instead of Merge Columns. The M concatenation operator is & and works on text columns only — numbers must be wrapped in Text.From().

// Example 1 — Full name with a space
// Add Column → Custom Column
= [FirstName] & " " & [LastName]
// Result: "Alice Brown"

// Example 2 — Composite key from two text columns
= [CustomerID] & "-" & [OrderID]
// Result: "C101-1001"  — useful as a unique row key

// Example 3 — Merging a number with text
// Numbers must be converted to text first using Text.From()
= [Category] & "-" & Text.From([SKU])
// Result: "ELEC-1042"

// Example 4 — Conditional merge
// Add a prefix only when a condition is met
= if [Status] = "Urgent" then "!! " & [OrderID] else [OrderID]
// Result: "!! 1001" for urgent orders, "1002" for normal

// Example 5 — YearMonth period label for sorting
= Text.From(Date.Year([OrderDate])) & "-"
  & Text.PadStart(Text.From(Date.Month([OrderDate])), 2, "0")
// Result: "2024-01", "2024-02" — sorts correctly as text

Column from Examples — The Shortcut

Column from Examples is the fastest way to extract or combine text when you know what the output should look like but do not want to write M code. You type example output values and Power Query reverse-engineers the transformation formula automatically.

Practice

Lesson Quiz

Next up — Lesson 22 covers Appending and Merging Queries — how to stack tables vertically with Append and join tables horizontally with Merge, including all six join types and the pattern for combining monthly files automatically.