How to Change Data Type in Direct Query Power BI

Working with Power BI's DirectQuery mode is a fantastic way to analyze massive datasets in real-time, but it comes with a common and frustrating roadblock: you can’t simply change a column's data type. If your dates are stored as text or your product IDs are numbers instead of text, you can’t just use the dropdown menu to fix it. This article will explain why this limitation exists and walk you through several effective methods to correctly format your data without abandoning DirectQuery.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Understanding the DirectQuery Limitation: Why Can’t You Just Click to Change?

First, it's helpful to understand what's happening behind the scenes. Unlike Import mode, where Power BI loads a full copy of your data into the report file, DirectQuery mode maintains a live connection to your original data source (like a SQL database, Azure Synapse, or Databricks). When you build a visual, Power BI doesn't look inside its own file for the data, it translates your request into the source’s native language (e.g., SQL) and sends a query directly to the database.

This "live" nature is what causes the restriction. Because Power BI doesn’t store the data, it can't modify it. Changing a column from text to a whole number isn't a simple operation it can translate into a query for every possible data source. Any transformation you apply in DirectQuery must be something Power BI can reliably convert into a compatible source query. Simple UI-based data type changes often don't make the cut, which forces us to find more explicit workarounds.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Best Solution: Making the Change at the Source

Before diving into any Power BI-specific tricks, the best practice and most robust solution is always to fix the data type in the original source system itself. If you're connecting to a SQL database and the OrderDate column is in a VARCHAR format, the ideal fix is to have a database administrator change it to a DATE or DATETIME format at the table level.

Why is this the best approach?

• Performance: The source database is optimized for handling data correctly. Performing calculations or conversions inside Power BI on every single query can add performance overhead, slowing down your reports. A fix at the source is a one-time change.
• Consistency: When you fix the data type in the source, every other report, application, or user connecting to that data gets the benefit of the correct formatting. It solves the problem for everyone, not just in your one PBIX file.
• Simplicity: It eliminates the need for workarounds inside your report. Your data model becomes cleaner, easier to understand, and easier to maintain.

Of course, you may not have permission or access to change the source database. If that's the case, don't worry - the following methods are excellent workarounds you can manage entirely within Power BI.

Workaround #1: Creating a DAX Calculated Column

The most common and flexible way to change a data type within Power BI while in DirectQuery mode is to use DAX (Data Analysis Expressions) to create a new calculated column. Instead of trying to change the original column, you leave it as is and create a brand new column that derives its values from the original but has the correct data type.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Convert Text to a Date with DAX

Let's say you have a table named 'Orders' with a column named OrderDateText that holds dates in "YYYY-MM-DD" format but is incorrectly stored as a text data type. Here’s how to create a proper date column:

1. Navigate to the Data View in Power BI Desktop (the second icon on the left pane).
2. Select the table containing your text-based date column.
3. In the "Table tools" ribbon at the top, click on New column.
4. In the formula bar, enter the following DAX expression:
5. Hit Enter. A new column named OrderDateCorrect will appear. Select this new column and make sure the Data type in the "Column tools" ribbon is set to Date.

The DATEVALUE function reads the text and converts it into a proper date/time value that Power BI can use for time intelligence, slicers, and axis sorting.

How to Convert Text to a Number with DAX

Similarly, if you have a sales amount column stored as text, you can convert it to a numeric type that can be properly aggregated (e.g., summed or averaged).

Imagine a 'Sales' table with a SalesAmountText column containing values like "$1,250.75" that you need to be a number.

1. Create a New column in the 'Sales' table.
2. Use the VALUE function in the formula bar:
3. Hit Enter. Power BI will create a numeric column called SalesAmountNumber. You can then set its data type to Decimal Number and format it as currency.

The VALUE function is quite smart and can typically handle currency symbols and commas within the text string.

How to Convert a Number to Text with DAX

Sometimes you need to do the opposite, such as treating a numeric ID like a product code or zip code as text so it isn't accidentally summed up.

If you have a 'Products' table with a numeric ProductIDNumber column, here's how to create a text version:

1. Create a New column in the 'Products' table.
2. Use the FORMAT function:
3. Hit Enter. The new ProductIDText column will now treat the numbers as text strings, preventing them from being used in unintended mathematical calculations.

Workaround #2: Trying Power Query M (When Query Folding Works)

"Query folding" is a process where Power Query transformations are translated back into the native language of the source (like SQL) and executed by the source system. In DirectQuery, a Power Query step must be able to fold, otherwise it will fail.

While many transformations don't fold, simple data type conversions sometimes do, depending on the source. You can try this method to keep the modeling logic within Power Query.

1. Click Transform data to open the Power Query Editor.
2. Select the table with the column you want to change.
3. Go to the Add Column tab and click Custom Column.
4. Give the new column a name (e.g., "AmountNumber") and enter a Power Query M formula, such as:
5. Click OK. Now for the crucial step.
6. Find the new "Added Custom" step in the Applied Steps pane on the right. Right-click on it. If the option View Native Query is enabled (not grayed out), it means the step has successfully folded! Your data source understood the transformation, and this method will work. If it's grayed out, the step could not be folded and this will throw an error when you try to apply the changes in DirectQuery.

This method is less reliable than DAX but can be a cleaner option when it works, as it keeps all transformation logic in one place.

Graphed

Still Building Reports Manually?

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

Watch the Demo

A Quick Guide: Which Method Should You Choose?

Feeling overwhelmed? Here's a quick cheat sheet to help you decide:

• Can you change the database? If yes, make the change at the source. This is always the best solution.
• Have no access to the source? The most reliable workaround is to use DAX calculated columns. It's flexible, powerful, and works in nearly all DirectQuery situations.
• Prefer keeping changes in the query editor? You can try using Power Query M, but always check if the step can "View Native Query" to ensure it's compatible with DirectQuery.

Final Thoughts

Changing data types in Power BI's DirectQuery mode may seem impossible at first glance, but it's entirely manageable. The key is understanding that you can’t alter the original source data, but you can create new, correctly formatted columns on top of it using DAX or, in some cases, Power Query. By fixing data at the source when possible, or using these DAX workarounds, you can build effective, real-time reports without compromise.

Frustrating conversion errors and query limitations are exactly why we built Graphed. We believe you should spend your time on insights, not on debugging data types. By connecting your data sources to Graphed, our AI-powered analyst handles the data preparation and transformation complexities for you. You can instantly create powerful, real-time dashboards just by describing what you need in plain English, allowing your entire team to ask questions and get answers without ever worrying about the differences between Import and DirectQuery mode again.