cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IMRGZ
Helper I
Helper I

Transform Date format without separation

Hi,

 

I have a question regarding the options to transform my specific date format into a 'normal' date format.

Dates are importes as numbers without separators like this (Dutch format):

18012019

9012019

However I would like these dates as follows:

18-01-2019

09-01-2019

 

I have checked the forum and other pages but I cannot find a solution. I have checked the options available under change type and then the options for "date" or "Using locale", but I can't find the right type of setting.

 

Any suggestions?

 

Thank you for your kind help.

2 ACCEPTED SOLUTIONS
jmalone
Resolver II
Resolver II

The day can be single digits ("9" in your example), but the months are always two digits ("01")?

 

To handle this, I've added a conditional statement that accommodates it. Otherwise if the days are always two digits, you can just use the first section (where Text.Length = 8).

 

This assumes your imported date field is a Text format (I used [SourceDate] as the field name...just replace it with the real column name).

 

Edit: I didn't mention at first, but you need to Add Column > Custom Column, then insert the following code:

 

if Text.Length([SourceDate]) = 8 
then
Text.Start([SourceDate], 2) 
&"-" & 
Text.Start( Text.End([SourceDate], 6) , 2)
& "-" & 
Text.End([SourceDate], 4)
else 
Text.Start([SourceDate], 1) 
& "-" & 
Text.Start( Text.End([SourceDate], 6) , 2)
& "-" & 
Text.End([SourceDate], 4)

 

View solution in original post

ChrisMendoza
Super User I
Super User I

@IMRGZ -

 

Personally I would convert to Date/Time Data Type and use the built-in formatting options.

 

DAX Calculated Column:

 

ConvertedToDate = 
VAR isLEN8 =
    IF ( LEN ( Table1[unformattedDate] ) = 8, TRUE (), FALSE () )
RETURN
    IF (
        isLEN8,
        DATE ( RIGHT ( Table1[unformattedDate], 4 ), MID ( Table1[unformattedDate], 3, 2 ), LEFT ( Table1[unformattedDate], 2 ) ),
        DATE ( RIGHT ( Table1[unformattedDate], 4 ), MID ( Table1[unformattedDate], 2, 2 ), LEFT ( Table1[unformattedDate], 1 ) )
    )

US formatting option allows me to format as yyyy-MM-dd.

 

 

https://docs.microsoft.com/en-us/dax/date-function-dax








Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

3 REPLIES 3
ChrisMendoza
Super User I
Super User I

@IMRGZ -

 

Personally I would convert to Date/Time Data Type and use the built-in formatting options.

 

DAX Calculated Column:

 

ConvertedToDate = 
VAR isLEN8 =
    IF ( LEN ( Table1[unformattedDate] ) = 8, TRUE (), FALSE () )
RETURN
    IF (
        isLEN8,
        DATE ( RIGHT ( Table1[unformattedDate], 4 ), MID ( Table1[unformattedDate], 3, 2 ), LEFT ( Table1[unformattedDate], 2 ) ),
        DATE ( RIGHT ( Table1[unformattedDate], 4 ), MID ( Table1[unformattedDate], 2, 2 ), LEFT ( Table1[unformattedDate], 1 ) )
    )

US formatting option allows me to format as yyyy-MM-dd.

 

 

https://docs.microsoft.com/en-us/dax/date-function-dax








Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

jmalone
Resolver II
Resolver II

The day can be single digits ("9" in your example), but the months are always two digits ("01")?

 

To handle this, I've added a conditional statement that accommodates it. Otherwise if the days are always two digits, you can just use the first section (where Text.Length = 8).

 

This assumes your imported date field is a Text format (I used [SourceDate] as the field name...just replace it with the real column name).

 

Edit: I didn't mention at first, but you need to Add Column > Custom Column, then insert the following code:

 

if Text.Length([SourceDate]) = 8 
then
Text.Start([SourceDate], 2) 
&"-" & 
Text.Start( Text.End([SourceDate], 6) , 2)
& "-" & 
Text.End([SourceDate], 4)
else 
Text.Start([SourceDate], 1) 
& "-" & 
Text.Start( Text.End([SourceDate], 6) , 2)
& "-" & 
Text.End([SourceDate], 4)

 

View solution in original post

@jmalone @ChrisMendoza 

Thank you for your help. Both solutions actually worked out nicely.

Indeed, I had 8 and 9 length digits.

I just had to copy in your codes with the right variables and it worked.

 

I am still really amazed about the quickness of response! Thanks again.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.