7. Data Cleaning A–Z in Power Query
7.10 Split Columns (All Six Methods, Plus Split into Rows)
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.
Ravindra Bagale's Tip – मराठी
हे बघा मित्रांनो: भागांची संख्या बदलत असेल (एका order मध्ये 3 items, दुसऱ्यात 7) तेव्हा columns मध्ये split केलं तर जास्तीचे भाग गुपचूप गायब होतात, कारण step तयार होतानाच columns ची संख्या ठरते. अशा वेळी rows मध्ये split करा. कोणत्याही split नंतर automatic Changed Type step तपासा, तो "01" चं 1 करू शकतो. पुन्हा एकदा करून बघा, मग पुढे जा.
Ravindra Bagale's Tip – हिंदी
देखो दोस्तों: जब हिस्सों की संख्या बदलती हो (एक order में 3 items, दूसरे में 7) तब columns में split करने से बाकी हिस्से चुपचाप खो जाते हैं, क्योंकि step बनते समय ही columns की संख्या तय हो जाती है. ऐसे में rows में split करो. किसी भी split के बाद automatic Changed Type step check करो, वह "01" को 1 बना सकता है. एक बार फिर करके देखो, फिर आगे बढ़ो.
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.