Date Formats

[robofski]

I'm in the UK

I have a field that I'm importing as YYYYMMDD. 

I'm writting reports that are mainly used in the US so I created a date field using:

DATEVALUE(mid(vw_PowerBI_Analysis[OrderCreateDate],5,2)&"/"&right(vw_PowerBI_Analysis[OrderCreateDate],2)&"/"&left(vw_PowerBI_Analysis[OrderCreateDate],4)

Which should have givien me mm/dd/yyyy

However in my model the the date is displaied as dd/mm/yyyy and when a US user views the published dashboard the dates continue to be dd/mm/yyyy.

Is there a way to have the date respect the user locale?

Thanks,

Dan

[kcantor]

@robofski

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.

[robofski]

@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?

[CahabaData]

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.

[kcantor]

@robofski

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.

[robofski]

@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!

[kcantor]

@robofski

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.