7.10 Split Columns (All Six Methods, Plus Split into Rows)

By Ravindra Bagale Last updated: 6 October 2026

Start with a problem

Customer data has Ravindra Bagale in one column and Kothrud-Pune in another. Rani wants to slice by City and greet customers by first name.

Explain simply

Split Column breaks one column into several: by delimiter, number of characters, positions, lower, then upper case, upper, then lower case, digit↔non-digit. Split into Rows puts each part on its own row.

Show the data

Before:

Customer ID	Full Name	Area City
C01	Ravindra Bagale	Kothrud-Pune
C02	Shraddha Bagale	Gangapur-Nashik
C03	Ruhi Bagale	Rajarampuri-Kolhapur
C04	Salman	College Road-Nashik

About this data: This table uses columns: Customer ID, Full Name, Area City. This table uses columns: Order ID, Items. This table uses columns: Area, City. This table uses columns: First, Last. Values shown are the lesson examples already on this page (not a new dataset).

Second example (pq_order_items_multi.csv):

Order ID	Items
BLK-801	Milk;Bread;Eggs
BLK-802	Curd
BLK-803	Bananas;Milk

Fictional sample data. Practice file: pq_customers_text.csv, pq_order_items_multi.csv (source: src/practice/).

Demonstrate

Select Area City › Split Column › By Delimiter › Custom - › Left-most delimiter › OK. Rename to Area and City.

Your result should look like this:

Area City

Kothrud Pune

Gangapur Nashik

Rajarampuri Kolhapur

College Road Nashik
Select Full Name › Split by delimiter Space › Left-most.

Your result should look like this:

First Last

Ravindra Bagale

Shraddha Bagale

Ruhi Bagale

Salman null
In the items query: Items › Split Column › By Delimiter ; › Advanced options › Split into Rows.

Your result should look like this:

Rows

6 (3 + 1 + 2)

Area	City
Kothrud	Pune
Gangapur	Nashik
Rajarampuri	Kolhapur
College Road	Nashik

First	Last
Ravindra	Bagale
Shraddha	Bagale
Ruhi	Bagale
Salman	null

Rows
6 (3 + 1 + 2)

Show the result

Split column by delimiter before and after, sample layout with fictional data

Your result should look like this (fictional sample):

City	Customers
Pune	1
Nashik	2
Kolhapur	1

Explain what happened

Left-most delimiter keeps College Road intact because only the first - is used. Split into Rows multiplies rows, one per item.

Explore further

The other split methods are in the reference below with examples.

Detailed reference for this lesson:

Split column by delimiter in Power Query with example — in 30 seconds

What: Split Column breaks one column into two or more columns at a character such as "-".

Why: You cannot slice by City if City and Area sit together in one text.

Tiny example: "Pune-Kothrud" becomes City = Pune and Area = Kothrud.

First look at the data (before, then after):

Split column by delimiter in Power Query with example: before and after tables, fictional Blinkit Maharashtra data

Same before / after tables as text

Before — Store column holds two facts

Store	Amount (₹)
Pune-Kothrud	500
Mumbai-Andheri East	400
Nashik-College Road	300

Formula / click path

Transform › Split Column › By Delimiter
Delimiter "-" · Split at: Left-most delimiter

After — After the split

City	Area	Amount (₹)
Pune	Kothrud	500
Mumbai	Andheri East	400
Nashik	College Road	300

Do it step by step:

Select the Store column.
Click Transform › Split Column › By Delimiter.
Choose --Custom-- and type -.
Choose Left-most delimiter. Click OK.
Rename the two new columns to City and Area.

एक-एक step करून बघा:

Store column निवडा.
Transform › Split Column › By Delimiter वर click करा.
--Custom-- निवडा आणि - type करा.
Left-most delimiter निवडा. OK करा.
दोन नवीन columns ना City आणि Area अशी नावं द्या.

एक-एक step करके देखिए:

Store column चुनिए.
Transform › Split Column › By Delimiter पर click कीजिए.
--Custom-- चुनिए और - type कीजिए.
Left-most delimiter चुनिए. OK कीजिए.
दो नए columns के नाम City और Area रखिए.

Transform › Split Column (also on Home) offers six methods. The examples below use our codes and product names.

Method	Before	After	Setting used
By Delimiter	BLK-PUN-KOT-01	BLK · PUN · KOT · 01	Delimiter "-", Each occurrence
By Number of Characters	BLK250314	BLK · 250314	3 characters, Once, as far left as possible
By Positions	20250314KOT	20250314 · KOT	Positions `0, 8`
By Lowercase to Uppercase	KothrudHub	Kothrud · Hub	–
By Uppercase to Lowercase	PUNkothrud	PUNk · othrud (usually not what you want)	–
By Digit to Non-Digit	500ml	500 · ml	–
By Non-Digit to Digit	Poha1kg	Poha · 1kg	–

Steps in Power BI – split Store ID by delimiter

Select Store ID (values like BLK-PUN-KOT-01).
Transform › Split Column › By Delimiter.
Select or enter delimiter: --Custom-- -; Split at: Each occurrence of the delimiter › OK.
Rename the new columns Platform Code, City Code, Area Code, Store No. Keep Store No as Text.
Tip: to keep the original column, use Add Column › Extract instead of splitting (7.12), or duplicate it first (Add Column › Duplicate Column).

Split = Table.SplitColumn(Source, "Store ID",
    Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
    {"Platform Code", "City Code", "Area Code", "Store No"})

Split into Rows

An order export sometimes lists all items of an order in one cell:

Before

Order ID	Items
BLK-40001	Ladi Pav; Amul Butter; Poha
BLK-40002	Nashik Grapes

After

Order ID	Items
BLK-40001	Ladi Pav
BLK-40001	Amul Butter
BLK-40001	Poha
BLK-40002	Nashik Grapes

Steps: select Items › Split Column › By Delimiter › delimiter (विभाजक चिन्ह, उदा. स्वल्पविराम) ; › expand Advanced options › Split into Rows › OK → Transform › Format › Trim.

ToLists  = Table.TransformColumns(Source, {{"Items", Splitter.SplitTextByDelimiter(";")}}),
ToRows   = Table.ExpandListColumn(ToLists, "Items"),
Trimmed  = Table.TransformColumns(ToRows, {{"Items", Text.Trim, type text}})

Ravindra Bagale's Tip

Look, friends: splitting into columns when the number of parts varies (3 items in one order, 7 in another) silently loses the extra parts, because the column count is fixed when the step is created. Split into rows in that case. After any split, check the automatic Changed Type step, which may turn "01" into 1. Try it once more, then move on.

Practice task

Simple bhashet sangaycha tar, split the Amazon Now Pack Size column ("500ml", "1kg", "12pcs") into Size Value and Size Unit using By Digit to Non-Digit, and set Size Value to Decimal Number.

Fix common mistakes

Wrong result	Reason	Correction
College Road split into three	Split at each occurrence of space	Use the correct delimiter and Left-most
Salman's last name is null	Only one word	Expected – keep null or fill with blank
Sales doubled after Split into Rows	Order amount repeats on every item row	Split items only in an item-level table

Practise independently

Practice task

How many rows does the items query have after Split into Rows, and how many contain Milk?

Hints

801 has 3 items.

Solution

6 rows; Milk appears in 2 (BLK-801, BLK-803).

Think like a professional

Splitting is easy; deciding the right grain afterwards (orders vs items) is the professional part.

Ravi Sir's Cards for this lesson

Split columns. Two facts in one column cannot be filtered separately.

Revision card

मराठी cards