Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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)
@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
Proud to be a Super User!
@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
Proud to be a Super User!
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)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |