📎 This article includes 2 downloadable practice files ↓
In this article
“ SHARMA TRADERS ”, “sharma traders” and “Sharma Traders” are the same customer to you and three different ones to a pivot table. Power Query’s text tools fix a whole column in a click, and repeat the fix on every refresh.
Format: trim, clean, case
Select the Customer column › Transform › Format:
- Trim: removes spaces at the start and end. (Unlike Excel’s TRIM, it doesn’t squeeze double spaces inside; use Replace Values “ ” → “ ” for that.)
- Clean: removes non-printing characters from exports.
- Capitalize Each Word, UPPERCASE, lowercase.
Select several columns first to apply the same step to all of them.
Replace values
Right-click a column › Replace Values: “Pvt. Ltd.” → “Pvt Ltd”, “N/A” → empty. Advanced options matches the entire cell only, so “North” doesn’t change inside “Northeast”.
Split column
The Location column holds “North – Delhi”. Right-click › Split Column › By Delimiter › Custom: - (space dash space) › Each occurrence. Rename the new columns Region and City (double-click the header).
Other options: by number of characters, by positions, by uppercase-to-lowercase changes, and into rows (one row per item for lists like “Pens, Files, Staplers”).
Check your work
Click the drop-down on the Customer column: the filter list shows distinct values. After cleaning, each customer appears once. View › Column distribution shows distinct and unique counts under each header.
Where beginners go wrong
| Mistake | Fix |
|---|---|
| Expecting Trim to remove double inner spaces | Replace ” ” with ” ” (repeat if needed) |
| Splitting at “-” when names contain hyphens | Split at the left-most delimiter only, or use ” – ” with spaces |
| Capitalize Each Word on codes like “GSTIN” | Apply case changes to name columns only |
Practice
Download the source file(s) and the expected-results workbook below. Build the query in Excel (Data › Get Data), load it to a sheet, and compare your row count and totals with the Checks sheet.
📎 Practice files for this article
- 🧾pq-02-messy-text.csvCustomer names in mixed case with spaces, and a combined "Region - City" column.⬇ CSV · 4 KB
- 📗Expected resultsWhat your query should produce, with check totals.⬇ XLSX · 8 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.