Changing Text and Splitting Columns
The Mumbai till lets cashiers type product names freely, so the export holds "masala chai ", "MASALA CHAI" and "Masala Chai". To Power BI those are three different products, each with its own small slice of revenue. On top of that, the branch column arrives as S2-Mumbai, with a code and a city glued together.
The rule
Trim first, fix the case next, split last.
The text tools sit under Transform → Format and, for new columns, Add Column → Format:
| Button | M function | " masala CHAI " becomes |
|---|---|---|
| Trim | Text.Trim |
"masala CHAI" |
| lowercase | Text.Lower |
" masala chai " |
| UPPERCASE | Text.Upper |
" MASALA CHAI " |
| Capitalize Each Word | Text.Proper |
" Masala Chai " |
| Clean | Text.Clean |
removes hidden control characters, such as line breaks |
Trim removes spaces from the start and end only; spaces between words stay. Capitalize Each Word does not trim, which is why the order matters.
In M, both fixes fit into one step. Inside each, the underscore _ stands for the current value:
= Table.TransformColumns(#"Changed Type", {{"Product", each Text.Proper(Text.Trim(_)), type text}})Split Column (on the Transform tab) breaks one column into several:
- By delimiter, at the left-most, the right-most or each occurrence of a character such as
-. - By number of characters, or at fixed positions.
- Into new columns (the default) or into new rows.
Splitting S2-Mumbai by - gives S2 and Mumbai, in columns named Store.1 and Store.2 until you rename them. If you only need one part, Add Column → Extract → Text Before Delimiter does it without touching the original (Text.BeforeDelimiter in M).
Every tool exists twice. On the Transform tab it changes the column in place. On the Add Column tab it builds a new column and leaves the original alone.
The shortcut
Clean in the order space → case → split. Spaces first, because a hidden trailing space spoils every later comparison. Case second, so identical names really match. Split last, once the pieces are tidy.
Trim Product
"masala chai "becomes"masala chai"."MASALA CHAI"has no extra spaces and is unchanged.
The trap is splitting at each occurrence when the delimiter can appear more than once. A new branch called S3-Navi-Mumbai would be cut into three pieces, breaking the city name in half. Splitting at the left-most - keeps S3 and Navi-Mumbai.
Drills
Answer fast — trust the rule.
What does Text.Trim(" Green Tea ") return?
What does Text.Proper("GREEN tea") return?
S3-Navi-Mumbai is split by - at the left-most delimiter. What are the parts?
Arjun needs a City column but must keep the original Store column too. Which tab should he use?
Why trim before removing duplicates on Product?
Remember
- Trim first, fix the case next, split last.
- Transform changes a column in place; Add Column keeps the original.
- Trap: splitting at each occurrence when the delimiter can appear inside a value.