Splitting & Merging Columns
Source systems love putting several facts in one column and splitting one fact across several. This lesson covers reshaping both directions — splitting by delimiter, position and character transition, merging into keys and labels, and Column From Examples for the cases where describing the rule is harder than demonstrating it.
Learning Objective
Splitting looks trivial until the delimiter appears inside the data, or the number of parts varies by row. Those two cases are where care is needed.
- Split a column by delimiter, by position and by character transition
- Choose correctly between splitting into columns and splitting into rows
- Handle a variable number of parts without losing data
- Merge columns into composite keys and display labels
- Use Column From Examples appropriately, and know its limits
Why This Matters
Three shapes that arrive constantly:
- One column, several facts.
NORTH-ELEC-2024-0042encodes region, category, year and sequence. You cannot slice by region until it is split out. - Several columns, one fact.
FirstNameandLastNamewhen you want a display name, or three columns that together form the only unique identifier. - One cell, several rows' worth of data.
"laptop; mouse; keyboard"in one order line. Each product needs its own row before it can be analysed.
Always check what happened to the rows that did not match the pattern.
Concept
Splitting a column
Transform › Split Column, or right-click the header. Five ways:
| Method | Splits on | Use for |
|---|---|---|
| By Delimiter | A character or string | The common case — commas, hyphens, semicolons, spaces |
| By Number of Characters | Fixed positions | Fixed-width codes where position is meaningful |
| By Positions | Specific character offsets | Fixed-width with irregular field widths |
| By Lowercase to Uppercase | A case transition | Camel-case values — OrderDate → Order Date |
| By Digit to Non-Digit | A character-class transition | Values like 12kg or A100 |
Split at which occurrence
Under Advanced options, By Delimiter offers a choice that matters:
- Left-most delimiter — splits once, at the first occurrence.
- Right-most delimiter — splits once, at the last occurrence.
- Each occurrence — splits at every occurrence.
Ali Hassan Khan on the space with each occurrence gives three columns. Splitting Ali Khan the same way gives two, and Power Query creates the column count from the first row it sees — so rows with more parts lose data and rows with fewer get nulls.For "first name and last name", right-most delimiter gives you everything before the last space and everything after it, which handles both cases and middle names correctly.
Split into columns versus into rows
| Into columns | Into rows | |
|---|---|---|
| Result | One row becomes wider | One row becomes several rows |
| Use when | The parts are different kinds of thing — region, year, sequence | The parts are several instances of the same thing — three products in one order |
| Example | NORTH-ELEC-2024 → Region, Category, Year | laptop; mouse; keyboard → three rows |
| Watch for | Variable part counts producing nulls or lost data | Row count multiplying — check it against expectation |
The delimiter-inside-the-value problem
The classic failure. A column contains "Smith, John" and you split on the comma. Fine. Then a row contains "Smith, John, Jr." and it produces three parts where your query expects two.
Worse: if the source is a CSV where values are quoted, "Acme, Inc." is one value — but only if the quoting was parsed correctly on load. If the delimiter was wrong at import, the damage happened before you got here.
- Check first. Add a column counting occurrences of the delimiter and look at the distribution. If it is not uniform, you know before you split.
- Prefer a bounded split. Left-most or right-most handles extra occurrences gracefully.
- Handle the exceptions explicitly rather than letting them fail quietly.
// how many parts would each row produce?
Table.AddColumn(Source, "PartCount",
each List.Count(Text.Split([FullCode], "-")), Int64.Type)
// then Group By PartCount to see the distribution.
// If every row is 4, split freely. If not, decide what to do about the others.Merging columns
Transform › Merge Columns (replaces the originals) or Add Column › Merge Columns (keeps them). You choose a separator and the new column name.
Two reasons to merge
| Purpose | Separator | Notes |
|---|---|---|
| A composite key | Something that cannot occur in the data — | is a common choice | When no single column is unique but a combination is. Module 4 needs this |
| A display label | A space, comma, or dash | FirstName + LastName → FullName |
NORTH||2024 rather than NORTH|ELEC|2024. Two different rows with nulls in different positions can collide into the same key.Before building a composite key: confirm no component is null, or replace nulls with an explicit token such as
[NONE] so the collision cannot happen.Column From Examples
Add Column › Column From Examples. You type what the output should be for the first few rows and Power Query infers a transformation.
| Good for | Bad for |
|---|---|
| Extracting a substring whose rule is fiddly to express | Anything where you cannot verify the inferred rule on every row |
| Reformatting dates or codes into a different shape | Business logic — it will infer something plausible and wrong |
| Learning the M function you needed — read the generated formula | Data where the pattern varies between rows |
Text.BetweenDelimiters or Date.ToText with the right arguments.Then edit that formula by hand so you understand and control it. Treating the generated expression as a starting point rather than a finished answer is the right relationship with this feature.
Visual Explanation
A real order-code column, split step by step.
Split into rows — when one cell holds several things
| Before | After |
|---|---|
ORD-1001 | laptop; mouse; keyboard | 1249.50 | ORD-1001 | laptop | 1249.50ORD-1001 | mouse | 1249.50ORD-1001 | keyboard | 1249.50 |
This is the single most common mistake after splitting into rows, and it produces a plausible wrong number. If the amount belongs to the order rather than the line, it must be handled separately: either split the amount too (if you know the breakdown), or keep order-level values in a separate order table at the correct grain.
Power BI Demonstration
List.Count(Text.Split([Column], "-")). Then Transform › Group By on that column to see the distribution of part counts.Column.1 tells nobody anything.|. Then verify uniqueness: column distribution should show distinct = unique = row count.Example Dataset
The FullName and Phone columns in this file are good practice for splitting. Phone in particular has four different formats, which makes it a realistic rather than a tidy exercise.
26 KB · 280 rows · 8 columns · CSV
Columns & data types
| Column | Data type | Meaning |
|---|---|---|
CustomerID | Text | Intended to be unique — it is not. Duplicates are your first clue. |
FullName | Text | Some entries have double spaces, trailing spaces, or ALL CAPS. |
Email | Text | Blank in some rows, malformed in others, whitespace-padded in others. |
Country | Text | 25 distinct spellings for 6 real countries. |
SignupDate | Text | Loads as text because five date formats are mixed together. |
LifetimeRevenue | Text | Loads as text: currency symbols and thousands separators present. |
Phone | Text | Four different formatting conventions. |
Status | Text | 7 spellings for 3 real states (Active, Inactive, Churned). |
Questions this dataset can answer
- How many genuinely distinct customers are in this file?
- What is total lifetime revenue once the column is numeric?
- How many customers are Active once status is standardised?
- Which rows should be rejected rather than repaired, and why?
Step-by-Step Exercise
+44 20 ..., (020) ..., 020..., +92 300 .... Try Column From Examples and inspect the formula it generates.Expected Result
| Check | Expected |
|---|---|
| Part-count distribution on FullName | Almost all 2; the dataset uses first + last names |
| Right-most split on a 3-part name | First two words in one column, last word in the other |
| Each-occurrence split on a 3-part name | Three columns; 2-part names get a null in the third |
| Column From Examples on Phone | Works for the two + formats, produces nonsense for the bracketed and plain ones |
| Composite key uniqueness | Distinct = unique = row count, once duplicates are removed |
| Proper-casing names | Damages any name with an internal capital — none in this dataset, but the risk is real |
+44 and +92 formats, the inferred rule will be "take characters 1 to 3" — which produces (02 for the bracketed format and 020 for the plain one.It does not error. It does not warn. It produces a column that is right for half the rows and wrong for the other half. Always test a generated formula against every format present in the data, not just the rows you used as examples.
Common Mistakes
Fix: Add a part-count column, Group By it, and look at the distribution before splitting.
Fix: Use right-most delimiter for first/last name splits. It handles middle names correctly and produces a consistent two columns.
0042 becomes 42 and the leading zeros are gone. Joins against the source system then fail.Fix: Codes are text, even when they look numeric. Only type as a number what you intend to do arithmetic on.
Fix: After any split into rows, check whether any numeric column has been duplicated. Keep order-level values in a separate table at order grain.
Fix: Read the generated formula, understand it, and test it against every distinct format in the column. Then rewrite it as a Custom Column you control.
Professional Tip
Column.1, Column.2, Column.3. Rename them immediately, before you do anything else.Two reasons. First, a query full of
Column.2 references is unreadable and every later step that mentions one becomes a puzzle. Second, and more practically: if the source changes and the split produces a different number of columns, renamed columns produce a clear "column not found" error naming a column you recognise, whereas Column.3 tells you nothing about what was expected there.One more:
- Choose a composite key separator that cannot appear in the data. A pipe (
|) or a rarely-used control sequence. If you merge with a hyphen and one component already contains a hyphen, two different rows can produce the same key — and a key collision is very hard to spot after the fact.
Knowledge Check
Knowledge Check
5 questions. Answer each one to see why the right answer is right.
You need to split Ali Hassan Khan and Chen Silva into first and last name. Which split option handles both correctly?
After splitting a product list column into rows, the order total is now three times too high. Why?
You split an order code and the sequence part is 0042. What type should it be?
What should you do before splitting a column by delimiter?
What is the most valuable use of Column From Examples?
Practical Challenge
Reshape an encoded transaction file
A source system exports one column holding an encoded transaction reference, and another holding a semicolon-separated product list. Build a query that reshapes it correctly and handles the awkward rows honestly.
- Create a sample file with a code column of the form
REGION-CAT-YYYY-NNNN, and a semicolon-separated product column - Include at least three awkward rows: one with a missing code part, one with an extra part, one with a single product
- Check the part-count distribution before splitting, and show the result
- Split the code into named columns, handling the awkward rows deliberately
- Split the product list into rows, and correctly handle the duplicated amount
- Build a composite key and prove it is unique
- State what you did with each awkward row and why
For the amount problem after splitting into rows, there are three defensible answers and you should pick one deliberately:
- Divide the amount evenly across the products — only valid if that is genuinely how the order was priced.
- Keep the amount on a separate order-level table and relate it — correct, and it means two tables at two grains.
- Null the amount on all but the first row — preserves the total but makes per-product analysis meaningless.
The third is a trap that looks tidy. Think about what happens when someone filters to one product.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
The approach
1 · Check the shape first
let
Source = #"Transactions Raw",
#"Count code parts" =
Table.AddColumn(Source, "CodeParts",
each List.Count(Text.Split([TxnCode], "-")), Int64.Type),
#"Count products" =
Table.AddColumn(#"Count code parts", "ProductCount",
each List.Count(Text.Split([Products], ";")), Int64.Type),
#"Shape distribution" =
Table.Group(#"Count products", {"CodeParts"},
{{"Rows", Table.RowCount, Int64.Type}})
in
#"Shape distribution"| CodeParts | Rows | Meaning |
|---|---|---|
| 3 | 1 | A missing part — needs investigating |
| 4 | 47 | The expected shape |
| 5 | 1 | An extra part — needs investigating |
2 · Handle the awkward code rows
I split with each occurrence but only after isolating the exceptions, so the main path is uniform:
#"Split good rows" =
let
Good = Table.SelectRows(#"Count products", each [CodeParts] = 4),
Bad = Table.SelectRows(#"Count products", each [CodeParts] <> 4),
SplitGood = Table.SplitColumn(Good, "TxnCode",
Splitter.SplitTextByDelimiter("-", QuoteStyle.None),
{"Region", "CategoryCode", "Year", "Sequence"}),
// exceptions keep the raw code and are flagged, not silently dropped
FlaggedBad = Table.AddColumn(Bad, "Issue",
each "Unexpected code shape: " & Text.From([CodeParts]) & " parts",
type text)
in
[Good = SplitGood, Bad = FlaggedBad]What I did with each awkward row, and why:
- The 3-part row — routed to the exceptions table, not split. Splitting it would put the year into the Sequence column and the category into Year, producing data that is wrong rather than missing. Wrong is worse than absent.
- The 5-part row — same treatment. The extra part might be a sub-region, or it might be a hyphen inside a category name. I cannot tell from the data, so I flag it and ask.
- The single-product row — no special handling needed.
Text.Spliton a value with no delimiter returns a one-item list, which splits into one row. This one is only awkward-looking, not actually awkward.
3 · The duplicated amount after splitting into rows
Orders — one row per transaction, holding TxnCode, the split code components, and Amount. This is where the money lives, at the grain it actually belongs to.OrderProducts — one row per transaction-product, holding TxnCode and Product. No amount column at all.Why not divide the amount evenly? Because I do not know that the products were equally priced, and inventing a per-product amount would create a number that looks authoritative and is fabricated. Someone would eventually build a "revenue by product" chart from it.
Why not null all but the first row? Because the total would be right and every filtered view would be wrong. Filter to "mouse" and revenue appears as zero for orders where the mouse was not listed first. That is a silent, filter-dependent error — the worst kind.
Two tables is more work and it is the only option that is honest at every grain. Module 4 relates them on
TxnCode.4 · The composite key
#"Add order key" =
Table.AddColumn(#"Split good rows", "OrderKey",
each Text.Combine({
[Region] ?? "[NONE]",
[CategoryCode] ?? "[NONE]",
[Year] ?? "[NONE]",
[Sequence] ?? "[NONE]"
}, "|"),
type text)The ?? "[NONE]" matters. Without it, two rows with nulls in different positions could both produce North||2024| and collide into one key. The explicit token makes every key unambiguous.
Proving uniqueness: select the OrderKey column and check column distribution — distinct must equal unique must equal row count. If it does not, the key is not a key and the model in Module 4 will not work.
What this challenge was really testing
Not the splitting — that is a few clicks. It was testing three judgements: checking the shape before committing, routing exceptions to a flagged table rather than forcing them through, and recognising that the duplicated amount is a grain problem, not a formatting problem.
The third is the one that separates people. The tidy-looking answers — divide evenly, or null all but the first — both produce numbers that are wrong in ways that only appear under filtering. Choosing two tables is less elegant and is the only answer that survives someone actually using the report.
Lesson Summary
- Check the part-count distribution before splitting. Power Query shapes the split from what it sees in the preview.
- Use right-most delimiter for first/last name splits — it handles middle names and gives a consistent two columns.
- Split into columns when the parts are different kinds of thing; into rows when they are several instances of the same thing.
- Split into rows duplicates every other column. Numeric values must be handled at the correct grain, usually in a separate table.
- Codes are text, even when they look numeric. Typing
0042as a number destroys it. - Rename split columns immediately.
Column.2is unreadable and produces useless errors. - Merge composite keys with a separator that cannot appear in the data, and replace nulls with an explicit token.
- Column From Examples writes M for you — read it, understand it, then control it. Never trust it untested across every format present.
Syllabus topics delivered
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.