Forum Discussion
Date Formats
You can do this in the query editor. For each date column complete the following steps:
Remove the existing Change Type step for the date.
Right click the date column, select change type, select using locale (at the bottom)
Change the data type to date
Change the Locale to English (United States).
You can also choose other formats from here as well. I would recommend duplicating the column and leaving both date types if the reports are used "at home" as well.
In other words, have a US Date column and a UK date column for users to access.
kcantorMany thanks.
Looking at how that works it would appear it won't accept yyyymmdd as the input so I'll have to transform the column first to a dd/mm/yyyy or am I missing something?
Well, that seems to be a bit of a problem. Here is the link to the article where I learned to fix my date issues. It is the same method but tells you how to change the source to make it correctible as well.
http://www.excelguru.ca/blog/2015/07/08/fix-date-errors/
If that doesn't work, Ken offers further methods for difficult dates in another article written a few days later.
http://www.excelguru.ca/blog/2014/06/23/importing-dates-in-power-query/
If that doesn't help, Ken has a forum for questions as well. In desperate times, I turn there for help myself. He is very helpful.
kcantorThanks for the pointers.
I've managed to get my yyyymmdd into text that looks like a US date using some M
(Text.Combine({Text.Range(Text.From([OrderCreateDate]),4,2),Text.Range(Text.From([OrderCreateDate]),6,2),Text.Range(Text.From([OrderCreateDate]),0,4)},"/"))
However, changing to a date using the English US Locale as per your suggestion, changes it back to UK format:
Frustrating!
I don't know why it is doing that. check here and see what they say: http://www.excelguru.ca/forums/forum.php
Let me know, though, in case I have this issue in the future.
In modeling - check to see if indeed the column is shown as "text". It probably is. Change that to 'Date'. Then in the Date/Time format selector you will be able to select the format you seek.
