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

By Ravindra Bagale Last updated: 25 September 2026

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

He bagha, mitrano: splitting into columns when the number of parts varies (3 items in one order, 7 in another) silently loses the extra parts, karan 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. Punha ekda karun bagha, mag pudhe jaa.

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.