📎 This article includes 3 downloadable practice files ↓
Advertisement
The ribbon covers common clean-ups. For anything else, Add Column > Custom Column with a little M.
| Need | M |
|---|---|
| Proper Case, trimmed | Text.Proper(Text.Trim([Name])) |
| Digits only | Text.Select([Phone], {"0".."9"}) |
| Last 10 digits | Text.End(d, 10) |
| Remove spaces | Text.Remove([Pincode], {" "}) |
| Number or blank | try Number.From(x) otherwise null |
Mobile = Table.AddColumn(Name, "Mobile", each
let d = Text.Select([Phone], {"0".."9"})
in if Text.Length(d) >= 10 and List.Contains({"6","7","8","9"}, Text.Start(Text.End(d, 10), 1))
then Text.End(d, 10) else null, type text)Indian mobile numbers start with 6-9, so the landline (0484) 2345678 correctly gives null.
⚠️ Our first version read “Rs. 2,250” as 0.225: keeping digits and dots kept the dot from “Rs.”, which became a decimal point. The fix removes “Rs.” first:
Text.Select(Text.Replace([Amount Text], "Rs.", ""), {"0".."9", "."}). Always check a few converted values against the source.Practice
Unzip the source files (e.g. to D:\PQ\). Try to build the query with the ribbon first, then compare with the M solution and the expected result. Every solution was run by Excel’s own Power Query engine before publishing.
📎 Practice files for this article
⬇ Download all 3 files (ZIP · 8 KB)
- 🗂️Practice source files (zip)All messy CSVs for the course: invoices, payments, typed customer names, credit history, contacts, branch files, a two-row-header report and an ERP register.⬇ ZIP · 3 KB
- 📄Lesson 5 M solutionThe full query. Paste into Advanced Editor and change the Folder line.⬇ PQ · 1 KB
- 📗Expected resultWhat the query returned when Excel ran it, to compare with yours.⬇ XLSX · 5 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.
Advertisement
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong