Forum Discussion
Date format - having excel file date format i.e. 27 January 2020 - need to be 27-01-2020
Importing a excel file to data model in excel.
All dates are like this:
31 January 2020
30 January 2020
2 February 2020
4 February 2020
I need in the format dd/mm/yyyy, like 31-01-2020
I am trying to change column format but it is being rejected.
Anyone who know what to do?
Hi LarsHansson
Jimmy801's solution should be helpful.
If not, please take advice below:
If you use "English(United States) under System setting->Time &Language->Region,
then use default setting for Power BI->Files->Options and settings->Region,
The date is of mm/dd/yyyy by default, it is hard to get date formated as dd/mm/yyyy.
One workaround is to create a calculated column which is of text format.
Column = FORMAT([Date],"dd-mm-yyyy")As tested, when change the setting to other language, for example, demark, which format for date is dd/mm/yyyy, then you could change the date format as expected.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hello LarsHansson
try out this solution. this should do the trick
let Source = #table ( {"Date"}, { {"31 January 2020"}, {"30 January 2020"}, {"2 February 2020"}, {"4 February 2020"} } ), TransformToDate = Table.TransformColumns ( Source, { { "Date", each Date.FromText(_,"en-GB"), type date } } ) in TransformToDateCopy paste this code to the advanced editor to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query, or I could create a custom function what makes it easier to apply if you are not used that much to power query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi LarsHansson
Jimmy801's solution should be helpful.
If not, please take advice below:
If you use "English(United States) under System setting->Time &Language->Region,
then use default setting for Power BI->Files->Options and settings->Region,
The date is of mm/dd/yyyy by default, it is hard to get date formated as dd/mm/yyyy.
One workaround is to create a calculated column which is of text format.
Column = FORMAT([Date],"dd-mm-yyyy")As tested, when change the setting to other language, for example, demark, which format for date is dd/mm/yyyy, then you could change the date format as expected.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Many thanks for all your imputs. Problem is now solved. Amazing how such a "small" thing can cause so much trouble. Much appreciated.
Hi LarsHansson
Click on Detect Data Type in Transform Ribbon.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
