How to Change Data Type in Power BI

Nothing stops a Power BI report in its tracks faster than an incorrect data type. You try to sum a sales column and get an error, your date slicer behaves strangely, or your charts simply refuse to show the right trends. This usually happens because Power BI sees your "Numbers" as "Text" or your "Dates" as "Whole Numbers." Learning how to fix this is a fundamental skill for building accurate and effective reports. This article will walk you through several clear, practical methods for changing data types in Power BI, focusing on the easiest and most effective approaches.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Correct Data Types are a Big Deal in Power BI

Before jumping into the "how," it's worth understanding the "why." Setting the right data type isn't just a technicality, it’s the foundation of your entire report. Here’s why it's so important:

• Accurate Calculations: This is the most obvious reason. You cannot perform mathematical operations like SUM, AVERAGE, or MIN on a column formatted as Text. If your sales revenue is stored as text, Power BI can't add it up. Similarly, you can't calculate the days between two dates if one of them is stored as text.
• Proper Visualizations: Power BI visuals are smart, but they need the right data type to work correctly. Line charts need a proper Date or Date/Time field on the x-axis to show trends over time. Bar charts and scatter plots require numeric values for their axes. If the data type is wrong, your visuals will be misleading or won't work at all.
• Effective Relationships: To build a successful data model, you need to create relationships between tables. Power BI requires the columns you're connecting to have the same data type. You can't connect a "Product ID" column formatted as a Whole Number in one table to a "Product ID" column formatted as Text in another.
• Model Performance: Using the most appropriate data type can optimize the size and performance of your model. For instance, using Whole Number instead of Decimal Number when you don't need fractions saves memory. This might seem small, but in large datasets with millions of rows, it adds up quickly.

Common Power BI Data Types Explained

Power BI supports a variety of data types. While you won't use all of them every day, you'll frequently encounter these core types:

• Decimal Number: This is the most flexible number type for values with decimal places, such as revenue ($49.99) or percentages (0.15).
• Whole Number: Use this for integers without decimal places, like quantities sold (50), year (2024), or employee IDs (1001).
• Text: Also known as a "string," this data type is used for any alphanumeric character data, like customer names, product descriptions, or state abbreviations ("NY").
• Date: This type is specifically for dates without time values (e.g., January 1, 2024). Using this type allows you to use date hierarchies (Year, Quarter, Month, Day) automatically.
• Date/Time: Contains both the date and time information (e.g., January 1, 2024, 10:30:00 AM). This is useful for analyzing events with specific timestamps.
• True/False: This is a Boolean type that can only hold one of two values: TRUE or FALSE. It’s perfect for flags like "IsActive" or "Subscribed."
• Binary: Used for storing binary data, most commonly images or files. It's less common for beginners to work with this directly.

The Best Place to Change Data Types: The Power Query Editor

The number one rule for data cleaning in Power BI is to do it as early as possible. That means using the Power Query Editor (sometimes called the "Transform data" window). Changes made here are applied to the data every time it's refreshed, ensuring your data model stays clean and consistent. It's the most robust and recommended place to manage data types.

To get there, click the Transform data button on the Home tab of Power BI Desktop.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: Click the Icon on the Column Header (The Fastest Way)

This is by far the quickest and most common way to change a data type inside the Power Query Editor.

1. Navigate to the Power Query Editor.
2. In the preview pane, find the column you need to modify.
3. Look at the column header. To the left of the column name, you'll see a small icon representing the current data type (e.g., ABC for Text, 123 for Whole Number, a calendar for Date).
4. Click on that icon. A dropdown menu with all available data types will appear.
5. Select the correct data type from the list. For example, if you have a column with numbers that is currently "ABC", select "Whole Number" or "Decimal Number."
6. A "Change Column Type" dialog box might appear. It will ask if you want to replace the existing step or add a new step. For beginners, it's often best to Add a new step. This allows you to easily undo the change later by deleting the step from the "Applied Steps" pane on the right.

Using this quick-click method, you can go through your columns and set them up correctly in seconds.

Method 2: Use the Transform Tab in the Ribbon

Another straightforward method uses the toolbar at the top of the Power Query Editor, known as the ribbon.

1. In the Power Query Editor, select the column you want to change by clicking on its header. The column will be highlighted.
2. Go to the Transform tab in the ribbon.
3. In the "Any Column" group, you will see a "Data Type:" dropdown.
4. Click the dropdown menu and select the new data type.

This process performs the same action as clicking the icon but is simply another way to access the feature.

Method 3: Use the Home Tab in the Ribbon

Like the Transform tab, the Home tab offers the same functionality for changing data types.

1. With the desired column selected in the Power Query Editor...
2. Make sure you are on the Home tab in the ribbon.
3. In the "Transform" group, you will also find the "Data Type:" dropdown.
4. Click it and choose the correct data type.

There's no functional difference between using the Home and Transform tab for this purpose, it’s a matter of user preference and workflow.

How to Fix Common Data Type Errors

Sometimes, simply trying to change the data type results in errors. This usually happens because the column contains values that don't match the new data type. Here’s how to handle two common scenarios.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Problem 1: Hidden Text in a Numbers Column

You have a "Sales" column that you want to set as a Decimal Number, but Power BI shows an error. Upon inspection, you find some cells contain text like "N/A" or "$1,234.56." The "$" currency symbol or a stray "N/A" prevents Power BI from converting the whole column to a numeric type.

The Solution: Use "Replace Values"

1. In Power Query, right-click the header of the culprit column.
2. Select Replace Values.
3. In the dialog box, put the character you want to remove in "Value To Find" (e.g., "$").
4. Leave "Replace With" blank to remove it entirely. Or, if you're replacing "N/A", you might replace it with "0". Click OK.
5. Repeat this step for commas or any other non-numeric text if needed.
6. Once all the text is removed, you can now change the data type to Decimal Number without errors.

Problem 2: Unrecognized Date Formats

You have a date column imported from a European source, and the dates are formatted as text like "31.12.2024" (Day.Month.Year). When you try to change the type to "Date," Power BI gives errors because it expects a format like "12/31/2024" (based on your regional settings).

The Solution: Use "Using Locale"

1. Click the data type icon on the column header (the "ABC" icon).
2. At the bottom of the dropdown list, select Using Locale...
3. A new "Change Type with Locale" window will open.
4. First, set the Data Type to what you want it to become (e.g., "Date").
5. Next, set the Locale dropdown to match the original format of the data. For "31.12.2024", you might choose "German (Germany)" or another European locale that uses that D.M.Y format.
6. Click OK. Power Query will now correctly interpret the original format and convert it to a proper date type.

An Alternative: Changing Data Types with DAX

While Power Query is the best practice for initial data transformation, you can also change data types using DAX (Data Analysis Expressions). It’s important to understand the key difference: DAX doesn't change the original column. Instead, it creates a new calculated column with the specified data type.

This method is useful when you've already loaded your data and want to create a quick variation of a column for a specific measure or visual without going back into the Power Query Editor.

When to use DAX for Data Types:

• When you need a temporary or secondary column with a different type for a specific calculation.
• When you need to dynamically format columns within your report.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Common DAX Functions for Type Conversion:

• VALUE( <,Text> ): Converts a text string that looks like a number into a number.
• INT( <,Number> ): Converts a number to an integer by rounding down.
• FORMAT( <,Value> , "&lt,Format&gt," ): Converts a value (number or date) into a text string in a specific format. Be careful: the result is always text.

Example: Creating a Numeric ID Column with DAX

Imagine your 'OrderID' column was incorrectly loaded as text, but you need it as a number for a calculation. You can create a new calculated column.

1. Go to the Data view or Report view in Power BI.
2. Select the table you want to add the column to.
3. From the "Table Tools" ribbon, click New Column.
4. In the formula bar, enter the following DAX formula:

Numeric Order ID = VALUE('YourTable'[OrderID])

This creates a new column called "Numeric Order ID" that contains the order IDs as numbers, ready for you to use in relationships or calculations.

Remember: Power Query is for permanently fixing your data at the source. DAX is for creating calculations and new columns on top of that cleaned data.

Final Thoughts

Mastering data types is a simple but powerful step toward becoming proficient in Power BI. Making sure your columns are correctly formatted from the start avoids countless headaches with calculations, visuals, and relationships down the line. Use the Power Query Editor as your main tool for this - clicking the column header icon is often the fastest way, while options like "Replace Values" and "Using Locale" solve nearly any error you'll encounter.

Spending hours cleaning data across different platforms before you can even start your analysis in a tool like Power BI is a common frustrating routine. Wading through messy CSVs, formatting columns, and troubleshooting import errors can feel like a full-time job. With us in your corner, you can connect your scattered data sources from Google Analytics, Shopify, Salesforce, and more in just a few clicks. You can simply ask for the dashboard you need, and Graphed builds it instantly, giving you back precious time to analyze insights instead of just finding and cleaning data.