Forum Discussion
Formatting date with inconsistent input data in same column
In the modeling tab in Model view, is it allowing you to change Format:dd/mm/yyyy?
There is the option for dd-mmm-yyyy and mm/dd/yy but there is not option for dd/mm/yyyy.
I.e. there is not option to have full number date format with day first.
To add some clarity to the issue in the image below I've duplicated the column, leaving the first column in text/num format and second in date format (mm/dd/yy) using the modeling ribbon rather than in the query editor.
As you can see it recognoises the ordering on month and day changes depending on whether the original column uses 8 or 08 for the month value.
.
Also this is what the data looks like in Excel. You see some recognised dates and some as text
Hi Anonymous ,
I disagree.
Mine has this one.
I am using English UK as default language for the report.
Hi mussaenda,
Unfortunately I do not share that luxury... Funny how it even teases me with the two US options. Also, I don't have admin privileges to change my OS system settings from US to UK.
Either way it wouldn't change the formatting issue but I would prefer dd/mm/yyHi Anonymous ,
You can try to change column type with 'using locale' option, it allow you to setting specific data type region to help power bi analysis records:
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}},"en-gb")Local date formats in Power BI
Regards,
Xiaoxin Sheng
