Here is the same article rewritten entirely in English, in a professional, technical documentation style — with no emojis or informal language.

Power BI: Converting Text Columns to Date in DAX (and Avoiding Type Conversion Errors)

1. Introduction

One of the most frequent issues in Power BI arises when a column is stored as text but is used in a DAX expression that expects date values.

This situation commonly occurs when calculating the difference between two dates using the DATEDIFF function, and it results in the error:

Cannot convert value ” of type Text to type Date.

This article explains why this happens, how to fix it directly in DAX, and how to prevent it through Power Query transformations.

2. Example Scenario

Assume you have a table named Requests with the following columns:

You need to calculate the difference between these two dates in weeks.

A typical DAX measure might look like this:

Weeks =
ROUND(
    ABS(
        DATEDIFF(
            'Requests'[StartDate],
            'Requests'[TargetDate],
            DAY
        ) / 7
    ),
    1
)

However, Power BI will display the error:

Cannot convert value ” of type Text to type Date.

3. Understanding the Cause

Power BI enforces strict data typing.
When a column is imported as text (for example, from Excel, SharePoint, or SQL), DAX does not automatically interpret it as a date.

Common reasons for the error include:

• Blank values ("") or invalid text (such as "N/A" or "Error").
• Text values that look like dates but are not properly recognized as date types.

The DATEDIFF function requires both arguments to be valid date or datetime values.

4. Correct Solution Using DATEVALUE

To fix this issue, you must explicitly convert the text column into a date value using DATEVALUE:

Weeks =
ROUND(
    ABS(
        DATEDIFF(
            'Requests'[StartDate],
            DATEVALUE('Requests'[TargetDate]),
            DAY
        ) / 7
    ),
    1
)

DATEVALUE() converts a text string that represents a date into a valid date value recognized by Power BI.

5. Handling Empty or Invalid Values

If some rows in TargetDate are empty or contain invalid text, the previous formula will still fail.
To prevent this, you can check whether the text value is blank before converting it:

Weeks =
VAR _targetDate =
    IF(
        TRIM('Requests'[TargetDate]) <> "",
        DATEVALUE('Requests'[TargetDate]),
        BLANK()
    )
RETURN
IF(
    NOT ISBLANK(_targetDate),
    ROUND(
        ABS(
            DATEDIFF(
                'Requests'[StartDate],
                _targetDate,
                DAY
            ) / 7
        ),
        1
    ),
    BLANK()
)

Explanation

6. Alternative: Convert in Power Query

In many cases, the best practice is to convert the data type in Power Query before loading it into the model.

Steps:

1. Open the Power Query Editor.
2. Select the column TargetDate.
3. Go to Transform → Data Type → Date.
4. Confirm the conversion.

If Power Query detects invalid values, use Replace Errors → null.

The M formula generated will look like this:

= Table.TransformColumnTypes(Source, {{"TargetDate", type date}})

This approach ensures that the column is already recognized as a date before it reaches DAX.

7. Best Practices

8. Quick Reference Table

9. Conclusion

Type conversion errors in Power BI are common when working with inconsistent data sources.
The key is to ensure data type consistency early in your data model.

For DAX expressions, combining DATEVALUE with validation logic (IF, TRIM, BLANK) provides a robust, error-free way to handle text-based date fields.

For optimal performance and stability, perform data type normalization in Power Query whenever possible.

10. References

• Microsoft Learn – DATEVALUE function (DAX)
• Microsoft Learn – DATEDIFF function (DAX)
• Microsoft Learn – Power Query M Reference
• Microsoft Learn – Handling data types in Power BI