How to Convert Text to Number in Power BI

Ever tried to create a chart in Power BI only to find your sales numbers acting like words instead of figures? This common snag, where Power BI reads numerical data as text, can bring your analysis to a screeching halt, preventing you from performing calculations, summaries, or sorts. This article will show you exactly how to fix it by converting text to numbers using both the Power Query Editor and DAX formulas.

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 Does Power BI Import Numbers as Text?

Before jumping into the solutions, it helps to understand why this happens. Power BI usually does a great job of detecting data types automatically, but it can get tripped up under a few conditions:

• Mixed Data Types: If a column contains both numbers and text (like "100", "250", "N/A"), Power BI will often play it safe and classify the entire column as text.
• Hidden Characters: Pesky extra spaces or non-printable characters hiding before or after a number can cause it to be read as text.
• Formatting Symbols: Currency symbols ($, €,), commas (,), or percentage signs (%) are great for human readability but can confuse the data type detection process.
• Regional Settings: A number like "1.234,56" means one thing in the United States and something completely different in Germany. If your data's regional format doesn't match Power BI's settings, you'll run into issues.
• Leading Zeros: Systems that export data like zip codes or ID numbers often format them as text to preserve leading zeros (e.g., "00123").

Luckily, fixing these issues is straightforward. We'll start with the most common and recommended method: Power 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

Method 1: The Quickest Fix using Power Query's "Change Type"

The Power Query Editor is Power BI's data transformation engine, and it’s the best place to handle data type conversions. Changes made here are applied to the data before it's loaded into your data model, which is the most efficient way to work.

Here’s the step-by-step process:

Step 1: Open the Power Query Editor From the main Power BI Desktop window, go to the Home tab and click on Transform data. This will launch the Power Query Editor in a new window, showing you all of your imported data tables.

Step 2: Select the Column You Want to Convert In the Power Query Editor, find and click on the header of the column you need to change. You can identify a text column by the "ABC" icon in its header. Number columns will have icons like "123" (Whole Number) or "1.2" (Decimal Number).

Step 3: Change the Data Type With the column selected, you have a few easy options:

• From the Home Tab: Look for the Data Type dropdown menu in the "Transform" section and select the appropriate number type.
• From the Transform Tab: Go to the Transform tab. The Data Type dropdown is the very first option on the left.
• By Right-Clicking: Simply right-click on the column header, hover over Change Type, and select your desired format from the list.

You'll see a few numerical options. Here’s what they mean:

• Whole Number: For integers without any decimals (e.g., 100, 5, -25).
• Decimal Number: For numbers with decimals (e.g., 99.99, 3.14).
• Fixed Decimal Number (Currency): Specifically designed for currency values. It's more precise than the standard decimal type to avoid rounding errors in financial calculations.

Select the one that fits your data. Power BI will ask you to confirm if you want to replace the current conversion step or add a new one. In most cases, selecting Replace current keeps your applied steps clean and easy to manage.

What If It Doesn’t Work? Troubleshooting Conversion Errors

Sometimes, simply changing the data type throws up an "Error" message in your cells. This means there's something in the column that Power BI can't recognize as a number. Don't worry! This is where the real data cleaning begins.

Fix 1: Remove Non-Numeric Characters with "Replace Values"

If your numbers contain symbols like "$", "%", or commas, you need to remove them first.

1. Right-click the column header and select Replace Values.
2. In the "Value To Find" box, type the character you want to remove (e.g., "$").
3. Leave the "Replace With" box empty to delete the character entirely.
4. Click OK.

Repeat this step for any other symbols in the column (like commas). Once the column is clean, try changing the data type to a number again.

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

Fix 2: Get Rid of Unwanted Spaces with "Trim" and "Clean"

Hidden spaces or invisible characters are a very common cause of conversion errors.

1. Right-click the column header you need to fix.
2. Hover over Transform.
3. Select Trim to remove any spaces at the beginning and end of the text.
4. Right-click the header again, hover over Transform, and select Clean to remove any non-printable characters.

Once you’ve trimmed and cleaned the column, proceed to change its data type.

Fix 3: Address Formatting with "Using Locale"

This is a powerful but often overlooked feature. If your data is from a region that uses a different numerical format (e.g., using a comma for a decimal point), Power BI might misinterpret it based on your computer’s local settings.

The "Using Locale" feature lets you tell Power BI how the data is supposed to be formatted.

1. Click the ABC icon in the column header.
2. In the dropdown menu, select Using Locale...
3. A dialogue box will appear. Here, you set two things:
4. Click OK. Power BI will now correctly interpret the numbers based on the locale you specified and convert the data type.

Method 2: Using DAX to Convert Text to Numbers

While Power Query is the preferred method for data transformation, there are times you might need to perform a conversion directly in the data model using DAX (Data Analysis Expressions). This is typically done when creating a calculated column after the data has already been loaded.

This is useful if you want to keep the original text column for reference or if the conversion logic is too complex for Power Query.

When to use DAX vs. Power Query for this? A simple rule of thumb: use Power Query for cleaning and preparing your raw data before analysis. Use DAX for adding calculations and business logic on top of your clean data.

Two primary DAX functions can help you convert text to numbers:

• VALUE(): Converts a text string that looks like a number into an actual number. It's smart enough to understand different number formats, like currency.
• INT(): Converts a text string into an integer, rounding down to the nearest whole number if decimals are present.

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

Creating a Calculated Column with the VALUE Function

Let's say you have a table named Sales with a text column called RevenueText that contains values like "$1,200.50".

1. Go to the Data view in Power BI Desktop (the grid icon on the left panel).
2. Select the table you want to add the column to.
3. From the Column tools tab at the top, click New column.
4. In the formula bar that appears, type the following DAX expression:

Numeric Revenue = VALUE(Sales[RevenueText])

Press Enter. Power BI will create a new column named "Numeric Revenue" containing the numerical values from the RevenueText column. You can then format this new column as currency from the Column tools tab to make it display correctly in your reports.

Final Thoughts

In this tutorial, we covered the main methods for converting text into numbers in Power BI, addressing common errors along the way. Your go-to solution should almost always be the Power Query Editor for its efficiency, but knowing how to use DAX functions like VALUE() provides an extra layer of flexibility for building your data model.

We know that data preparation is often the most time-consuming part of analytics. Manually fixing data types, cleaning columns, and combining sources are exactly the kind of repetitive tasks that slow down teams. This is a core problem we built Graphed to solve. You just connect your data sources, and our AI-powered analyst handles the data cleansing and preparation automatically. Instead of wrestling with columns in Power Query, you can simply ask questions in plain English like, "show me total revenue by month," and get a live, interactive chart instantly.