Number to date

[Broeselchen]

Ok, I'll try it this https://community.powerbi.com/t5/Desktop/Zahl-in-Datum-%C3%A4ndern-Zellen-mit-0-Werten/m-p/3008604#M... in english:

I want to change a number into a date. It only works, if there isn't a 0 in the column (KAHJMT). But, if there is a 0, it's a problem (KAGDAT).

[Samarth_18]

Hi @Broeselchen ,

Please replace blank with BLANK() in your code. It should be like below:-

Kagdat_datum =
IF (
    Customers[KAGDAT] = 0,
    BLANK (),
    DATE ( LEFT ( Customers[KAGDAT], 4 ), MID ( Customers[KAGDAT], 5, 2 ), RIGHT ( Customers[KAGDAT], 2 ) )
)

[Broeselchen]

No, sorry:

"An argument of the "DATE" function is of the wrong data type, or the result is too long or too short."

I tried it "KAGDAT" as number and as text.

[Samarth_18]

@Broeselchen Please try this:-

Kagdat_datum =
IF (
    Customers[KAGDAT] = 0 || Customers[KAGDAT] = BLANK() ,
    BLANK (),
    DATE ( LEFT ( Customers[KAGDAT], 4 ), MID ( Customers[KAGDAT], 5, 2 ), RIGHT ( Customers[KAGDAT], 2 ) )
)

[Broeselchen]

No, same problem : /

[Samarth_18]

@Broeselchen Please confirm do you have anything in the column apart from 0 and 8 character value e.g.(YYYYMMDD)?

[Broeselchen]

No, there's nothing else.

[Samarth_18]

@Broeselchen is it possible for you to share PBIX file after removing sensitive data?

[Broeselchen]

Oh, now I made the list shorter and it works. So, you're right, there must be something in the dates. How can I finde the problem?

(I can't find a point to upload the csv...)

[Samarth_18]

@Broeselchen you can upload it any drive and share it with open access or you can use website like https://wetransfer.com/ 

[Broeselchen]

https://we.tl/t-IszDRZCMfh

[Samarth_18]

Hi @Broeselchen , We are getting this error due to you have some date which are 00.

[Broeselchen]

Ok! And do you have any solution for this problem?

[Samarth_18 · Accepted solution]

@Broeselchen You can replace with day as 01 if its 00. Use below code for it.

Kagdat_datum = 
var _day = if(int(RIGHT ( [KAGDAT], 2 ))=0,01,RIGHT ( [KAGDAT], 2 ))
return IF (
    [KAGDAT] = 0 || [KAGDAT] = BLANK() ,
    BLANK (),
 DATE ( LEFT ( [KAGDAT], 4 ), MID ( [KAGDAT], 5, 2 ),_day)
)

[Broeselchen]

Yes!!!! Thank you so much!