cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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

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
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

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors