Power Query Text & Phone Number Cleaning

If you’ve ever dealt with user-generated data, web form submissions, CRM exports, or notes from a sales team, you’ve likely come across what I call "dirty text", fields full of extra line breaks, odd spacing, hidden characters, and unpredictable formats.

Power Query can be used for taming messy data (but really there is no substitute for better inputs). In this post, I want to share how I’ve built a reusable toolkit which can live inside Power BI (or Excel), featuring two custom functions:

• One to mask phone numbers

• One to clean and normalise unstructured text

Masking Phone Numbers with Power Query

When you're working with customer or user data, it's often necessary to mask personal information, either for privacy reasons, internal review processes, or any other anonymised dataset req (make sure you are not gathering information which you do not need).

Here’s a lightweight custom function I built in M that extracts the digits from any phone-like string, and hides the final portion for privacy:

let

    MaskPhone = (input as nullable text) as nullable text =>

    let

        // Handle nulls

        SafeInput = if input = null then "" else input,

        // Remove all non-digit characters

        DigitsOnly = Text.Select(SafeInput, {"0".."9"}),

        // Define how much to show (here we use the first 3 digits)

        ShowPrefixLength = 3,

        TotalLength = Text.Length(DigitsOnly),

        // Mask the rest with Xs

        Masked =

            if TotalLength >= 9 and TotalLength <= 12 then

                Text.Start(DigitsOnly, ShowPrefixLength) &

                Text.Repeat("X", TotalLength - ShowPrefixLength)

            else

                input // fallback if length is out of bounds ie outside 9-12 characters

    in

        Masked

in

    MaskPhone

You can plug this function into your Power Query workflow, then use it in a Table.AddColumn step to mask any free-text phone field. It works on a wide variety of inputs, including:

• "027-456-7890"

• "(03) 123 4567"

• "Mobile: 021 555 5555"

…and returns a neat version like 027XXXXXXX.

Cleaning Text with Unprintables, Weird Spacing, and Line Breaks

Text fields are another common culprit. You might not even see the problem at first, but users will. It can show up as:

• Random blank lines

• Inconsistent spacing

• Symbols that look fine but break filters or visuals

• Notes copied from PDFs or old systems that include unprintable ASCII characters

  
  

I built a Power Query function that handles all of this. It does the following:

• Removes carriage returns, tabs, non-breaking space, null characters, escape characters

• Trims each line, but retains legitimate line breaks for readability

• Collapses repeated spaces to a clean, standard structure

• Discards completely blank lines

Why Not Just Use Text.Trim or Text.Clean?

Those built-in Power Query functions are helpful, but they don’t catch everything. For example:

• Text.Clean doesn’t remove non-breaking spaces

• Text.Trim can’t collapse repeated internal spaces

• Neither function handles multi-line formatting with nuance

Sometimes you need specific control, especially when working with notes or system exports.

Want the Text Cleaner Code?

If you’d like the full fnCleanText function (it’s a bit longer than the phone masker), I’m happy to share it, just send us a message and we will send it through.

Why the gate? Mostly because I want to make sure:

• You get the right version for your use case

• You understand how to install and tweak it

• You don’t copy/paste something you don’t fully control

But also, I spent time on this and I think it is a valuable tool, we run a business and would like to talk to people. We wont sales pitch you but if you see value in what we do, we might be a good fit to help your business.

Wrap Up

If you’re serious about keeping your Power BI reports clean, polished, and user-friendly, reusable functions like these can save you hours, and make your work look that much more professional.

Start with the phone number masker, it’s easy to drop into your workflow. And if you deal with untidy text: message me for the full cleaner function and I’ll hook you up.