Forum Discussion
Date Format
How can I make this text format in a dd mm yy format? Using the normal DateValue it seems to give me dates in future eg 7 Dec instead of 11 July?
15 Replies
Hi Chippy
Use Change Type -> Using Locale then choose a locale that uses the d/m/y format e.g. Australia or the UK
NOTE: In this image I've chosen the Data Type : Date, if you want to preserve the time part of your data, choose Date/Time instead
Regards
Phil
Thanks, This did not seem to work..I am wondering if it is due to the date formats not being consistent eg days drop the leading zero but months have a leading zero.
Hi Chippy
No that shouldn't matter - download this file for an example.
When you say it didn't seem to work - why do you say this? What result did you get when you tried what I suggested?
Please provide the actual data you are using - even better would be the file with the data in it.
Regards
Phil
I am not sure if this is the best way to sahre as I can not attach a file..bit in this sample you can see the original dates when converted to datevalue reverses some dates from dd mm to mm dd
Thanks Phil, Yes, I took your approach and now using Power Query instead of the DAX and I seem to have more success.
Another query has come up, that is: my data is sourced from another 3rd party application, so I need to download the data in Excel. I tried downloading the data and saved over the old data, keeping the same file name but the BI model says, "The key didn't match any rows in the table". What would be the best practise for refreshing the excel data, to ensure all the added custom queries I have added in Query are preserved?
thanks
Chris
Hi Chippy
Best to start a new post for a different problem and accept a solution for this one.
That error can indicate that names of sheets or columns are different in the file(s) you are trying to import compared to the initial file you used to set up the query.
Best practice is to keep all files you import with the same query to have exactly the same structure (e.g. sheet names, column names) and data types in the columns.
Regards
Phil
