Forum Discussion
Date Format Error
Hi,
Hope someone can help me. I have looked through many forums but didn't come across this one.
My regional settings is English (United Kingdom) and Data load type detection is set to below.
My data source is excel file from an application which presents US date format like below.
When I load the data, it appears as text format.
The date format ideally is the desired one (I want it to stay as US formatted only). So, I thought either of below may help but didn't 😞
1. Simply change the format to Date. When I do it, below error happens. Important thing to note here is that error only comes up for dates after 12th of the month.
2. Or, I click the Date column > using locale > Date type as "Date" and Locale set to "English (US)". Strangely, it rather converts dates into United Kingdom format which is not what I want. See below.
3. Changing my regional setting to English (United States) and then change the format of Date column to "Date". But it still appears in UK format (exactly as in 2nd attempt above).
I would appreciate if someone can look into it and help fixing this. I spent hours already. Any guidance will be appreciated. Thanks.
Also, please find attached pbix and excel file in use for this.
https://1drv.ms/u/s!Aq2FeLHchvwogoFqQJywpf7U8Y8kKw?e=L8mfDt
regards,
Gaurav
23 Replies
GK600 - I am not sure I entirely follow this but when I get dates in a European format in the forums and need to convert it, I do the following:
- In Power Query, split by /
- Change all created columns to Text
- Concatenate them back in the order I want in a new column, [2] & "/" & [1] & "/" & [3]
- Convert to date
- Remove other columns
This is all in Power Query.
Thanks Greg_Deckler for your repsonse.
I actually don't need date to convert into UK format. I want it to stick to US format (that's how it appears but with "text" as data type when loaded into Power BI). The moment, I change the data type to "Date", it starts to cause issues.
I anyway tried as you suggested but strangely when I changed it to "Date" as Data type, it converts it into UK format (I also tried using locale). Please see below. This is quite weird as I do have US as regional setting for this file. Not sure why its pushing it to UK format.
Any pointer will be helpful.
regards,
Gaurav
GK600 - What are your regional settings File | Options and settings | Options | GLOBAL | Regional settings and also CURRENT FILE | Regional Settings. Also check if Model language has been set.
GK600 , power bi pickup date time setting from the system. I think you have this setting
If yes, on option is make it US setting.
else you have create date from this
new date =
var _pos = search("/",[Date],,0)
return
date(right([date],4), left([date],_pos-1), mid([date],_pos+1,2))Thansk amitchandak for your response. I need it to adjust it within Power Query as some further transformation needs to be done based on this Date column.
I think you suggested DAX for Power BI desktop. Can you please suggest what can be done within Power Query. Thanks. Appreciate you taking time to respond.
Gaurav
GK600 The dates in your excel file are actually stored as text, you can tell that by the left alignment of a number. Numbers are always right aligned in excel a quick and dirty way is to do this, since excel is accessible to you. Once you covert them back to numbers using double negative, you can replace the original column with the new one:
Hi AntrikshSharma ,
I don't agree modifying date column from TEXT to NUMBER data-type in excel, because Power BI can easily handle the date values that come as Text through a excel file.
Like see below raw data from excel:
Now I can transform this TExt Date column to date type by using Locale option and it gets converted quite easily:
I used United Sates date format "Using Locale" because I know they are in that format, and Power BI easily converts the text to date value:
Here my default Date format is United Kingdom, therefore dates are shown in this format.
Let me know if I mis-understood your response. 🙂
Thanks,
Pragati
Hello @Pragati11 ,
Agreed, but the thing is his dates are in a different format, see the apostrophe before the date, that kind of format isn't changed by using using locale. Can you create same scenario in your system and let me know if it works for you.
An ez solution I found is to split the column by the separator "/", now u have dd mm yyyy, after that u add all of them into a new column with this "Text.PadStart(Text.From([Day]), 2, "0") & "/" & Text.PadStart(Text.From([Month]), 2, "0") & "/" & Text.From([Year])", with this u can change it into date.
