How to Clean Text Columns in Power Query with Split, Trim, Clean and Lowercase

By Ravindra Bagale · Last updated: 16 October 2026

Practice file: blinkit-products.csv: 9 fictional products with the mess shown below. The same table is used in How to Merge Tables in Power BI Power Query.

Made-up data: the products and brands (Sahyadri Foods, Krishna Dairy, Godavari Mills, Mauli Sweets) are invented for practice. They imitate a quick-commerce product list from Maharashtra; no real company is meant.

1. The problem

The product file as it arrives (spaces shown as ·, a tab as →):

You cannot make a "revenue by brand" chart because brand is stuck inside the product text. "Dairy" and "DAIRY" would show as two categories. P104 has no name at all. (The size column is handled in the merge chapter.)

2. The idea in simple words

• Split Column cuts one column into two at a delimiter, the text that separates the parts. "Masala Chai Biscuit (Sahyadri Foods)" split at " (" gives "Masala Chai Biscuit" and "Sahyadri Foods)".
• Split by " (" (space and bracket), not just "(". Otherwise the product keeps a space at its end.
• Clean removes characters you cannot see, such as tabs and line breaks. Trim removes spaces at the start and end. lowercase makes "GHEE" and "Ghee" the same. Run these three on every text column you will filter, group or match on.
• A blank product is still a product that people order. Do not delete the row; give it a clear label such as "unknown product", so its orders stay in every total.

3. The data

Expected result for the 9 rows, which you can check by eye:

Distinct values after cleaning: 4 brands plus "unknown brand", 4 categories (snacks, dairy, staples, sweets).

4. Step by step

Load

1. Home › New Source › Text/CSV › blinkit-products.csv › OK. Rename the query to products.
2. If the headers are in row 1 (type detection off), Home › Use First Row as Headers.

Split product and brand

3. Select product (brand). Home › Split Column › By Delimiter.
4. Choose --Custom-- and type ( (a space, then an opening bracket). Split at: Left-most delimiter. Open Advanced options, Quote Character: None. OK.
5. Rename the two new columns: product (brand).1 → product, product (brand).2 → brand.
6. Select brand. Transform › Extract › Text Before Delimiter, type ), OK. "Sahyadri Foods)··" becomes "Sahyadri Foods".

Split category

7. Select category || sub_category. Split Column › By Delimiter › Custom || (space, two bars, space) › Each occurrence of the delimiter › Quote Character None › OK.
8. Rename the parts to category and sub_category.

The three text steps

9. Ctrl+click product, brand, category and sub_category.
10. Transform › Format › Clean. The tab after "Godavari Mills" is gone.
11. Transform › Format › Trim. Leading and trailing spaces are gone.
12. Transform › Format › lowercase.

Find and label the blank product

13. View › tick Column quality, Column distribution and Column profile. Under product and brand you see one Empty value.
14. Click the product cell for P104. If it is empty text, right-click product › Replace Values, leave Value To Find empty, Replace With unknown product, OK.
15. The brand cell for P104 shows null in italics. Right-click brand › Replace Values › Value To Find: type null › Replace With unknown brand › OK. (An empty Value To Find does not match null cells.)

5. Your result should look like this

The products query matches the table in section 3: 9 rows, no Empty values in product, brand, category or sub_category (Column quality shows 100% Valid), and every text value in lowercase, including the unknown labels.

Column distribution for brand: 5 distinct values (sahyadri foods, krishna dairy, godavari mills, mauli sweets, unknown brand). For category: 4 distinct.

6. What happened

• Split by " (" cut each value at the space before the bracket, so product has no trailing space and brand starts with the brand's first letter.
• Extract Text Before ")" dropped the bracket and anything after it, including the spaces and tab at the end of P101 and P103.
• Clean removed the hidden tab; Trim removed the spaces before "GHEE" and after "Shrikhand 500 g (Krishna Dairy)"; lowercase turned "DAIRY" into "dairy" so it groups with "dairy".
• For P104 the split had nothing to cut: product became empty text and brand became null, which is why the two Replace Values steps needed different Value To Find entries.
• Row count stayed 9: you fixed values and kept every product.

7. Explore further

• Left-most vs each occurrence. Left-most splits once into two columns, safe when a product name could contain a second bracket. Each occurrence splits at every delimiter and can create more columns.
• Quote Character None. With the default (") a delimiter inside quotes is ignored. Product names with inch marks can then fail to split; None treats every character the same.
• Why lowercase and not Capitalize Each Word? Any one consistent case works for matching. Lowercase is the safest because it does not depend on where words start. For display you can still format labels later.
• Clean vs Trim. Clean removes non-printable characters anywhere in the text; Trim removes spaces only at the start and end, not between words.
• Column profile is based on the first 1,000 rows by default. For big tables switch to Column profiling based on entire data set at the bottom of the editor.
• How many orders hide behind a blank product? In the 401-order practice data from the append chapters, P104 has 43 orders worth ₹5,340. Deleting it would remove them all.

8. Common mistakes

9. Practise on your own

10. Think like a professional

• Business view. A brand or category column is only useful if every spelling of it is one value. The three text steps are what make "revenue by brand" correct.
• Performance. Cleaner text means fewer distinct values, which compresses better in the model.
• Maintenance. Keep the text steps together and name them ("Text clean: Clean, Trim, lowercase"). When next month's file arrives with new spaces, the same steps fix it.
• Unknowns are a to-do list. Share the list of unknown products with the team that owns the product master, so they can be named at the source.

Interview questions

1. What is the difference between Trim and Clean? Trim removes leading and trailing spaces; Clean removes non-printable characters such as tabs and line breaks.
2. How do you split "Name (Brand)" into two columns? Split Column by the custom delimiter " (", then Extract Text Before ")" on the second part.
3. Why not delete rows with a blank product name? Their orders would disappear from every total; label them unknown instead.
4. How do you replace null cells with Replace Values? Type null in Value To Find.

Recap

Ravi Sir's Cards for this lesson

Revision cardComparison card

मराठी cards