How to extract numbers from text strings in Power BI?

Use case

I have a column in my database in which i have an employee IDs that is mixed with text (e.g., ‘EMP12345’ or ‘EMPLOYEE12345’ for example). The text length is not fixed. How can I extract just the numeric part in Power BI for further analysis and visualization?

Introduction

Extracting numeric data from text strings is a common challenge in data preparation. Power BI offers robust tools to handle this task efficiently, both in Power Query and using DAX in Power BI desktop. This post explores how to solve this problem using an test dataset as an example.

Step-by-step Implementation

1. Power Query Solution

a. Open Power Query Editor
b. Select the EmployeeID column
c. Add a custom column with the following M code:

= Text.Select([EmployeeID], {"0".."9"})

d. Rename the new column to “NumericEmployeeID”
e. Change the data type of “NumericEmployeeID” to Whole Number

Note: we always prefer to manipulate data as close as possible to the data source (SQL / Excel / Data Lakes etc’), hence using Power Query for data cleansing is advised.

2. DAX Solution

If the option of using Power Query is not viable for your data conversion, you can also manipulate your data with DAX:

a. Create a new calculated column in Power BI Desktop
b. Use the following DAX formula:

NumericEmployeeID = 
CALCULATE(
    MAX(
        VALUE(
            SUBSTITUTE(
                SUBSTITUTE(EmployeeData[EmployeeID], "EMP", ""),
                "emp", ""
            )
        )
    )
)

Remember

• Text.Select in M: Filters characters based on a condition
• SUBSTITUTE in DAX: Replaces specified text with another
• VALUE in DAX: Converts text to a number

Extract numbers without losing signs, decimals, or identifiers

The safest approach is to define the intended result before changing settings. Define whether minus signs, decimal separators, percentages, and multiple numbers belong in the result. Keep the current file, profile, or configuration available so recovery remains possible.

Compare one condition at a time

Keep account numbers and codes as text when leading zeros matter. Then use Power Query for refresh-time parsing and DAX only when model context requires it. A controlled comparison is more useful than a reinstall because it identifies whether the boundary is the account, data, client, device, or service.

Protect data and permissions

Test blank, null, error, and mixed-locale strings separately. Preserve the original text beside the extracted value. If a setting is locked or labeled as organization-managed, capture the message and ask the responsible administrator instead of bypassing policy.

Confirm the outcome

Count conversion errors before replacing or filtering them. Close and reopen the application, then repeat the ordinary workflow with a noncritical sample. Check synchronization or scheduled refresh when timing affects the feature.

For escalation, record the exact action, result, app version, identity type, and time. Share only sanitized screenshots. A second authorized user’s result can reveal hidden owner privileges or stale state. Undo experiments that did not help and retain the known-good configuration until the fix works consistently. Recheck the result after the next sign-in or scheduled cycle when timing can affect it. Keep a concise note of the successful condition so it can be restored later. Verify that the change did not alter unrelated files, permissions, or settings. Recheck the result after the next sign-in or scheduled cycle when timing can affect it. Keep a concise note of the successful condition so it can be restored later. Verify that the change did not alter unrelated files, permissions, or settings.

What can go wrong?

1. Inconsistent formats: If some IDs don’t follow the “EMP12345” pattern, modify the M code to handle variations:

   = Text.Select([EmployeeID], {"0".."9"})

2. Performance issues with large datasets: Consider extracting numbers in Power Query instead of DAX for better performance.
3. Blank results: Verify that all EmployeeID values contain numbers. Add error handling in DAX using IFERROR if needed.