Forum Discussion
Date
How to handle this date column? Some are dd-mm-yyyy format and some are mm-dd-yyyy format.
Hi MHTANK ,
You can click in Power BI Query, right click [StockDate] - Change Type - Date/Time.
When the date is wrong, the wrong date will display Error.
If Error is displayed, you can display the correct date by doing the following:
In Power Query check [StockDate] - Split Column - By delimiter.
Result:
Create calculated column.
Month = IF( 'Sheet1'[StockDate.2]>=12,[StockDate.3],[StockDate.2])Day = IF( 'Sheet1'[StockDate.2]<>[Month],[StockDate.2],[StockDate.3])Date = DATE( [StockDate.1],[Month],[Day])Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Are yoy sure they are mixed up? the values displayed in the image could all be mm-dd-yyyy
And if they are mixed up, how can you tell that 12-01-2018 is either or....?
Yes , I am sure
so is 12-02-2018 mm-dd-yyyy or dd-mm-yyyy?
Hi MHTANK ,
You can click in Power BI Query, right click [StockDate] - Change Type - Date/Time.
When the date is wrong, the wrong date will display Error.
If Error is displayed, you can display the correct date by doing the following:
In Power Query check [StockDate] - Split Column - By delimiter.
Result:
Create calculated column.
Month = IF( 'Sheet1'[StockDate.2]>=12,[StockDate.3],[StockDate.2])Day = IF( 'Sheet1'[StockDate.2]<>[Month],[StockDate.2],[StockDate.3])Date = DATE( [StockDate.1],[Month],[Day])Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
