Forum Discussion
Convert date into fix format
I have a date column like below:
The format is mix up. For example, the correct format should be DD.MM.YYYY. Row 1-3 are correct but 4th row is showing differnet format MM.DD.YYYY.
How can I convert the the 4th row to DD.MM.YYYY?
Thanks for your explanation. The IF Then statement is how the year, month and day sequence based on DATE formula and the output display is based on our compute setting.
As you have raised caution regarding the display result may be have conflict. For example, 02/12/2022 (text) can be 2nd December 2022 but actual should be 12nd February 2022, especially there are many rows with different date format in single column.
Thus, the suggested DAX cannot handle it effectively. What we can do to solve this besides mannually change it in Excel?
9 Replies
alvin199 , first of all, has power bi taken it has date. Then you can use the format of your choice using the format option.
I doubt it is still text or power bi has not taken it in the current format
refer
DD/MM/YYYY to MM/DD/YYYY
date(year( right(DD__MM__YY[date],4)), month(mid(DD__MM__YY[date],4,2)) ,day(left(DD__MM__YY[date],2)))MM/DD/YYYY to DD/MM/YYYY
date(year( right(DD__MM__YY[date],4)), month(left(DD__MM__YY[date],2)),day(mid(DD__MM__YY[Version_Id],4,2)) )I encounter errror.
The original data type for Date column is text by Power BI.
Also, using this formula will create error for the 4th row as its format is not the same with the first 3 row.
I am able to extract the date for the first 3 row but it display weird result on the 4th row.
If need to use IF Then statement, not sure how to incorporate it correctly
Hi alvin199
If you don't manage to get the other solutions to work, you can create a Calculated Column as follows:
Date Correction =
VAR _DD = VALUE ( LEFT ( 'Table'[Date] , 2 ) )
VAR _MM = VALUE ( MID ( 'Table'[Date] , 4 , 2 ) )
VAR _YYYY = VALUE ( RIGHT ( 'Table'[Date] , 4 ) )
RETURN
IF ( _MM > 12 , DATE ( _YYYY , _DD , _MM ) , DATE ( _YYYY , _MM , _DD ) )Outputs per below:
Basically, the above formula checks the middle to determine if the month is greater than 12 which creates a switch. The only issue you will have is that if you have a large dataset and there is many of these date errors, it's likely that irrespective of what approach you take, there may be the situation that arises where you have 11/3/2022 which may be 3 November 2022 or 11 March 2022. Please take caution to this.
All the best.
Theo
