Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🧹 Module 3 · Power Query & Data Transformation Intermediate LESSON 19 / 72

Splitting & Merging Columns

27 min 5 quiz questions 1 practical challenge 4 syllabus topics

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.

Lesson 19 of 72 · Power Query & Data Transformation 26%
01

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.

By the end of this lesson you will be able to
  • 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
02

Why This Matters

Three shapes that arrive constantly:

  • One column, several facts. NORTH-ELEC-2024-0042 encodes region, category, year and sequence. You cannot slice by region until it is split out.
  • Several columns, one fact. FirstName and LastName when 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.
The trap in all three
Splitting is easy when the data is uniform. It is the non-uniform rows that cost you — a delimiter inside a value, a missing part, an extra part. Those rows either error or, worse, split into the wrong columns and look fine.

Always check what happened to the rows that did not match the pattern.
03

Concept

Splitting a column

Transform › Split Column, or right-click the header. Five ways:

Split methods
MethodSplits onUse for
By DelimiterA character or stringThe common case — commas, hyphens, semicolons, spaces
By Number of CharactersFixed positionsFixed-width codes where position is meaningful
By PositionsSpecific character offsetsFixed-width with irregular field widths
By Lowercase to UppercaseA case transitionCamel-case values — OrderDate → Order Date
By Digit to Non-DigitA character-class transitionValues 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.
Right-most is the answer more often than people expect
Splitting 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 columnsInto rows
ResultOne row becomes widerOne row becomes several rows
Use whenThe parts are different kinds of thing — region, year, sequenceThe parts are several instances of the same thing — three products in one order
ExampleNORTH-ELEC-2024 → Region, Category, Yearlaptop; mouse; keyboard → three rows
Watch forVariable part counts producing nulls or lost dataRow 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.

  1. 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.
  2. Prefer a bounded split. Left-most or right-most handles extra occurrences gracefully.
  3. Handle the exceptions explicitly rather than letting them fail quietly.
Power Query M · Check delimiter counts before splitting
// 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

PurposeSeparatorNotes
A composite keySomething that cannot occur in the data — | is a common choiceWhen no single column is unique but a combination is. Module 4 needs this
A display labelA space, comma, or dashFirstName + LastName → FullName
Composite keys and null
Merging columns where one is null produces a value with a missing part — 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.

When it earns its place, and when it does not
Good forBad for
Extracting a substring whose rule is fiddly to expressAnything where you cannot verify the inferred rule on every row
Reformatting dates or codes into a different shapeBusiness logic — it will infer something plausible and wrong
Learning the M function you needed — read the generated formulaData where the pattern varies between rows
Use it as a teacher
The most valuable thing about Column From Examples is that it writes the M for you and shows it. Give it two examples, look at the generated formula, and you have just learned 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.
04

Visual Explanation

A real order-code column, split step by step.

Split into rows — when one cell holds several things

Before and after Split Column → By Delimiter → Rows
BeforeAfter
ORD-1001 | laptop; mouse; keyboard | 1249.50ORD-1001 | laptop | 1249.50
ORD-1001 | mouse | 1249.50
ORD-1001 | keyboard | 1249.50
Notice what happened to Amount
The amount was duplicated onto every new row. Summing it now gives 3,748.50 instead of 1,249.50 — triple the truth.

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.
05

Power BI Demonstration

Check the shape before splitting
Add Column › Custom Column: List.Count(Text.Split([Column], "-")). Then Transform › Group By on that column to see the distribution of part counts.
Split by delimiter into columns
Right-click the header → Split Column › By Delimiter. Choose the delimiter and, under Advanced options, Each occurrence. Rename the resulting columns immediately — Column.1 tells nobody anything.
Try right-most on a name column
Split a full-name column on the space using Right-most delimiter. Compare against Each occurrence on a row containing a middle name.
Split by character transition
On a camel-case column, use Split Column › By Lowercase to Uppercase. Useful for system-generated field names.
Split into rows
On a column containing a delimited list, Split Column › By Delimiter, then under Advanced options choose Rows instead of Columns. Check the row count before and after.
Deal with the duplicated numeric column
After splitting into rows, note that any amount column has been copied to every new row. Decide deliberately what to do about it.
Merge into a display label
Select two columns → Add Column › Merge Columns, separator = space, name = FullName. Note that Add Column keeps the originals.
Merge into a composite key
Select the key components → Add Column › Merge Columns, custom separator |. Then verify uniqueness: column distribution should show distinct = unique = row count.
Try Column From Examples
Add Column › Column From Examples › From selection. Type the desired output for two rows. Look at the formula it generates in the bar at the top — that is the real value.
Copy the generated formula and use it directly
Cancel out, then create a Custom Column and paste the formula. Now you control it and you understand it.
TransformSplit ColumnBy DelimiterAdvanced options
06

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.

bwb-messy-customers.csv
26 KB · 280 rows · 8 columns · CSV
Business context
A deliberately dirty extract — the kind of file that actually lands in your inbox. It combines two exports, so it contains duplicate rows and duplicate customer IDs. Country is spelled 25 different ways for 6 real countries. Dates use five different formats. Revenue mixes currency symbols, thousands separators and blanks. Status has 7 spellings for 3 real states. Emails are missing, malformed or padded with whitespace. Every Power Query lesson uses this file.

Columns & data types

ColumnData typeMeaning
CustomerIDTextIntended to be unique — it is not. Duplicates are your first clue.
FullNameTextSome entries have double spaces, trailing spaces, or ALL CAPS.
EmailTextBlank in some rows, malformed in others, whitespace-padded in others.
CountryText25 distinct spellings for 6 real countries.
SignupDateTextLoads as text because five date formats are mixed together.
LifetimeRevenueTextLoads as text: currency symbols and thousands separators present.
PhoneTextFour different formatting conventions.
StatusText7 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?
07

Step-by-Step Exercise

Split FullName safely
Use right-most delimiter on the space to produce FirstName and LastName. Check what happens to any single-word or three-word names.
Count the parts first
Before splitting, add a part-count column and group by it. How many names have 2 parts? How many have 3? How many have 1?
Compare the two split strategies
Split the same column with each occurrence and see how the result differs for three-part names.
Extract the country dialling code from Phone
The formats vary — +44 20 ..., (020) ..., 020..., +92 300 .... Try Column From Examples and inspect the formula it generates.
Decide whether the generated formula is safe
Test it against all four formats. Does it produce something sensible for each, or does it work for two and produce nonsense for the others?
Build a composite key
Merge CustomerID with the standardised Country using a pipe. Verify uniqueness.
Build a display name
Merge FirstName and LastName back with a space, proper-cased. Compare against the original column and note any names the proper-casing damaged.
08

Expected Result

Expected findings
CheckExpected
Part-count distribution on FullNameAlmost all 2; the dataset uses first + last names
Right-most split on a 3-part nameFirst two words in one column, last word in the other
Each-occurrence split on a 3-part nameThree columns; 2-part names get a null in the third
Column From Examples on PhoneWorks for the two + formats, produces nonsense for the bracketed and plain ones
Composite key uniquenessDistinct = unique = row count, once duplicates are removed
Proper-casing namesDamages any name with an internal capital — none in this dataset, but the risk is real
The Phone result is the important one
Column From Examples will confidently produce a formula from your two examples. If both examples happened to be +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.
09

Common Mistakes

Splitting without checking the part count first
Power Query creates columns based on what it sees in the preview. Rows with more parts lose data; rows with fewer get nulls. Neither is announced.

Fix: Add a part-count column, Group By it, and look at the distribution before splitting.
Using 'each occurrence' for names
Three-part names produce three columns and two-part names produce nulls, so the query is shaped by whichever rows happened to be in the preview.

Fix: Use right-most delimiter for first/last name splits. It handles middle names correctly and produces a consistent two columns.
Typing a code column as a number after splitting
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.
Forgetting that split-into-rows duplicates other columns
An order amount is copied onto every new row, so summing it multiplies the total by the number of items.

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.
Trusting a Column From Examples formula without testing it
It infers from the examples you gave. If those did not cover every format present, the rule is wrong for the rest — silently.

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.
10

Professional Tip

Rename split columns in the same step you create them
Splitting produces 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.
11

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?

12

Practical Challenge

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.

Requirements
  • 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.

13

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

Power Query M · Part-count check before splitting
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"
The distribution it reveals
CodePartsRowsMeaning
31A missing part — needs investigating
447The expected shape
51An extra part — needs investigating
This is the whole point of checking first
Without this step you would split, get five columns because Power Query saw the 5-part row in the preview, and end up with 47 rows containing a null in the fifth column and one row with data misaligned across the others. Both look plausible in a preview.

2 · Handle the awkward code rows

I split with each occurrence but only after isolating the exceptions, so the main path is uniform:

Power Query M · Route exceptions away from the happy path
    #"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.Split on 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

The choice, and the reasoning
I split into two tables at two grains.

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

Power Query M · Composite key with null protection
    #"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.

14

Lesson Summary

Key takeaways
  • 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 0042 as a number destroys it.
  • Rename split columns immediately. Column.2 is 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

Splitting columnsMerging columnsColumn from ExamplesSplit into rows
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 26% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.