How to Convert Text to Number in Power BI DAX

Dealing with data types in Power BI can be tricky, especially when numbers you need to calculate are disguised as text. If you’ve ever tried to sum a column of sales figures and gotten an error or noticed your chart sorting "10" before "2," you've probably run into this exact issue. This article will show you exactly how to convert text to a number in Power BI using DAX functions, giving you clean, usable numbers for your reports and dashboards.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Convert Text to a Number In the First Place?

Before diving into the “how,” it’s important to understand the “why.” When Power BI sees a number stored as text (often appearing as left-aligned in the data view), it treats it like a word. This causes several problems:

• You can’t do math. You cannot perform mathematical operations like SUM, AVERAGE, MIN, or MAX on a text column. Trying to do so will result in an error because Power BI doesn’t know how to add up words.
• Sorting is incorrect. Text fields sort alphabetically, not numerically. This means "1," "10," "100" would sort before "2" and "25," leading to confusing and inaccurate visualizations. Correct numerical sorting is 1, 2, 10, 25, 100.
• Relationships don’t work properly. If you're trying to build a relationship in your data model between a numeric T-ID in one table and a text-based T-ID in another, it may fail or perform poorly.
• Incorrect chart rendering. Many chart types require numerical values for their axes. A text-based column can't be used as a value in a bar chart or line chart, severely limiting your visualization options.

Imagine you’ve just imported your latest sales data from a CSV file. The "Revenue" column includes dollar signs and commas (e.g., "$1,500.25"). Because of this formatting, Power BI might have interpreted the entire column as text. You won’t be able to calculate your total revenue until you convert this column into a proper number format.

Easiest Method: Use the Power Query Editor First

While this article focuses on DAX, the best practice for permanent data type changes is to handle them before the data even loads into your model. The Power Query Editor (the "Transform Data" window) is the perfect tool for this initial clean-up.

Using Power Query is almost always more efficient for fixing an entire column because the change is applied once when the data refreshes. Using DAX creates a new calculated column, which uses more memory and processing power.

Here’s how to quickly change a data type in Power Query:

1. Click on Transform data from the Home ribbon to open the Power Query Editor.
2. In the editor, find and select the column you want to convert.
3. On the Home tab (or the Transform tab), you'll see a Data Type dropdown menu. It will likely say "Text" (ABC icon).
4. Click the dropdown and select the appropriate number format, such as Decimal Number or Whole Number.
5. Power Query will ask if you want to replace the current conversion step or add a new one. In most cases, selecting Replace current keeps your applied steps cleaner.
6. Click Close & Apply on the Home tab to load the clean data into your model.

So, when should you use DAX? Use DAX when you need to perform conversions dynamically within a measure or create a calculated column based on logic after the data is already loaded.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Converting Text to Numbers with DAX Functions

If you need to perform the conversion within your data model - for instance, in a calculated column or inside a measure - DAX provides a few powerful functions. We’ll focus on the two most common and useful ones: VALUE and INT.

Using the VALUE Function for General Conversion

The VALUE function is your go-to tool for converting a text string that looks like a number into an actual numerical value. It's quite flexible and can often interpret common numerical formatting like currency symbols and commas, depending on your system's regional settings.

Syntax: VALUE(<text>)

Let's use our earlier example of a 'SalesData' table where the 'Revenue' column is text. To create a new, numeric version of this column, follow these steps:

1. In Power BI Desktop, go to the Data View (the grid icon on the left).
2. Select the table containing your text-formatted numbers.
3. From the Table tools ribbon, click New column.
4. In the formula bar, type the following DAX expression:

Revenue (Numeric) = VALUE(SalesData[Revenue])

Hit Enter. Power BI will create a new column called "Revenue (Numeric)" containing the revenue values as a proper number format. You can now use this new column in your calculations and visualizations.

Handling Potential Errors with VALUE

What happens if your text column contains values that VALUE can't convert, such as "N/A," hyphens, or blank cells? This will cause an error in your calculated column for that specific row.

To prevent your report from breaking, you can wrap the VALUE function inside an error-handling function like IFERROR. It lets you define a fallback value to use if an error happens.

Here’s an improved formula that substitutes any conversion error with a 0:

Revenue (Numeric) Safe = IFERROR(VALUE(SalesData[Revenue]), 0)

This is much more robust. If a revenue entry is "Not Available", this formula will return 0 instead of showing an error. Be mindful of your reporting context - sometimes replacing errors with BLANK() (IFERROR(VALUE(SalesData[Revenue]), BLANK())) is more appropriate so it doesn’t skew averages.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Using the INT Function for Integers

If you know your text strings represent whole numbers and you want to specifically convert them to integers, the INT function is a great choice. It works similarly to VALUE but is designed for integers and will truncate (cut off) any decimal values without rounding.

Syntax: INT(<text>)

Imagine an 'Employee' table with an 'Employee ID' column that was mistakenly imported as text. Since IDs are whole numbers, INT is perfect.

To create a numeric Employee ID column:

Employee ID (Numeric) = INT(Employee[Employee ID])

Note: The INT function is much stricter than VALUE. It generally cannot handle non-numeric characters like currency signs or commas. It expects text that cleanly represents a number. If your string is "$1,500.75", INT('Table'[Column]) would error out, whereas VALUE might succeed. If it processed "1500.75", INT would return 1500.

Advanced Scenario: Cleaning Text Before Conversion

Sometimes, the number you need is buried inside a longer text string. For example, you might have product SKUs like "PROD-10023" or order IDs like "ORD_8841". In these cases, you need to first extract the numeric part using text manipulation functions and then convert it.

Let’s say you have a 'Products' table with an 'SKU' column formatted like "PROD-xxxxx".

You can combine SUBSTITUTE and VALUE to clean and convert it in one step:

SKU (Numeric) = VALUE(SUBSTITUTE(Products[SKU], "PROD-", ""))

• SUBSTITUTE(Products[SKU], "PROD-", "") is evaluated first. It finds the text "PROD-" within the SKU and replaces it with nothing (""), leaving just the number string (e.g., "10023").
• The VALUE function then takes that resulting string and converts it into a proper number.

Best Practices and Common Pitfalls

To use these functions effectively, keep these key points in mind:

1. Clean Upstream in Power Query When Possible

It's worth repeating: Power Query should be your first choice for fixing data types. It’s more efficient and keeps your data model cleaner. Use DAX for calculated columns or measures when the logic requires it or when you cannot modify the source query.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

2. Handle Errors Gracefully

Your data is rarely ever perfectly clean. Always anticipate potential errors from blanks, "N/A" values, or other text your functions cannot convert. Always wrap your DAX conversion formulas in IFERROR to control the output and prevent your visuals from breaking.

3. Be Aware of Regional Settings

The VALUE function’s behavior can be influenced by your computer’s locale settings for thousands separators (comma vs. period) and decimal points. What works for "$1,234.56" in a US setting may fail in a European setting where the format is "€1.234,56". Always test your conversions on a sample of your actual data.

4. Avoid Creating Redundant Calculated Columns

A calculated column uses memory and increases the size of your .pbix file. Don't add a calculated column just to convert a data type if you could have done it in Power Query with one click. Think of DAX solutions as a scalpel for precise in-model calculations, not a hammer for initial data prep.

Final Thoughts

Transforming text to numbers is a fundamental skill for anyone working in Power BI. Understanding the difference between cleaning data in Power Query and performing calculations with DAX will help you build more efficient and reliable reports. With functions like VALUE, INT, and some clever error handling, you can conquer messy source data and unlock accurate insights.

While mastering DAX can be powerful, we know that data preparation can feel like a chore that steals time away from real analysis. We built Graphed to completely remove this friction. Instead of wrestling with data types or writing formulas line-by-line, you can connect your data and just ask for what you need in plain English. Graphed handles the data wrangling for you, instantly building live dashboards from your questions so you can spend less time cleaning and more time deciding.