Cannot convert value '44253' of type Text to type Date.

[Anonymous]

Hi Experts,

I am getting an error while converting data type from "Text" to "Date/time".  Please find the below data for reference.

Go-live/End-date
16-02-2021
14-02-2021
44527
44265
Not Applicable
Not Applicable
Not Applicable
Not Applicable
Not Applicable
Not Applicable
44264

I have a Date column in my table and the data type is "Text". Now i want to convert integer values "44527" to "Date" Data type in Power BI and need to show these "44527" value as "Date" in my report. I tried to change the type Text to Date but got another error for "Not Applicable", but i also need to show "Not Applicable" as well.

Could anyone help on this issue?

Best Regards,

Srihari

[Anonymous · Accepted solution]

Hi @Anonymous ,

Sorry for my misunderstanding, please try this:

Measure =
VAR _int =
    IFERROR (
        CONVERT ( MAX ( 'Table'[Go-live/End-date] ), INTEGER ),
        MAX ( 'Table'[Go-live/End-date] )
    )
RETURN
    IFERROR ( CONVERT ( _int, DATETIME ), MAX ( 'Table'[Go-live/End-date] ) )

Here is the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous ,

Please try the following formula to create a measure:

Measure =
IF (
    CONTAINSSTRING ( MAX ( 'Table'[Go-live/End-date] ), "-" ),
    CONVERT ( MAX ( 'Table'[Go-live/End-date] ), DATETIME ),
    "Not Applicable"
)

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous

Thanks for the quick response. But in my scenario I have to convert integer values (44265,44527 ) as Date data type because in my data source file (SharepPoint)"Go-live/End-date" column contains date data type only but while importing data from source to Power BI then Power BI engine reading these date values as text data type and it is aggregating the values in Go-live/End-date column.
I want to show final output like below one.

Please Note, I laso need to show "Not Applicable" as one of the value for respective rows. So, whenever I tried to convert from Text to Date type in power query I am getting an error for "Not Applicable" as well.
So I want to achevive it using DAX. Could you please provide me the required output?

Best Regards,
Srihari

[amitchandak]

@Anonymous , You have to check the format in power query or check for all number and then add these days to 01-01-1900 and check