Forum Discussion
Date Columns recognized as Text not "Date"
Power BI Newbie looking for a little help...
I am trying to import an excel file with two columns each of which are meant ot be "dates", but are being recognized as text. When trying to format them in the ribbon, I receive an Error saying We can't automatically convert the column to Date type.
If I try a second time I receive a message:
Upon selecting Yes, I receive the same error.
I tried reformatting it in Excel before re-importing, tried making it a csv - but not sure how else to have Power BI recognized this as a date.
All proposed solutions are welcome.
Thanks in advance.
Anonymous
The first row is your header? ..before to apply this you have to "Use first row as header"
16 Replies
- Vvelarde
Community Champion
Anonymous
Maybe is an error because the format of the date.
What format have?
- Phil_Seamark
Microsoft Employee
Check your regional settings in Power BI. File -> Options and Settings - >Options -> Regional Settings.
Playing with this may help.
Also double check if you have any really werid data in your date column that cannot be parsed.
Make sure you are doing the converstion in the Query Editor too.
Thanks Phil - keep in mind I am a newb.
Regional settings are properly setup. Nothing apparently weird in the date column.
I am working with a flat file, uploading it. When selecting Edit during the "Get Data" process, I still am not able to transform the data when modifying the Data Type from Text to Date.
I have to imagine this is an error that is fairly common. Any additional thoughts?
v-yulgu-msftHi Anonymous,
To dertermine whether there existing any werid data (string value) in your date column that caused the failure to import data into Power BI, please recreate a table in excel which only includes one date column, add a few date records for a test.
Besides, as I cannot reproduce your issue, please share the whole date column in your excel file for more analysis.
Thanks,
Yuliana GuHere is a link with sample data as requested in a csv file. Thanks in advance.
https://www.dropbox.com/s/6iez3cohthg42c7/Sample%20Text%20Date%20Data.csv?dl=0
- Vvelarde
Anonymous
Hi, This is a problem about configuration of date. In your data have mm/dd/yyyy and when try to convert to Date gives you error because the software wants to translate using dd/mm/yyyy. The error appears when you want to convert a date like 01/31/2015 and don't exist a date with the month 31.
To solve this please follow these steps
1: Go to Edit Query:
Go to Edit Query
2. Select the two columns, Right Click --Change Type - Using Locale
3. Select in Data Type: Date and Set the Locale (in your case you can use English - United States)
4. Ready Close & Apply
- Aamir_khan
Kudo Collector
what is the solution ?
I had the same problem with the errors. What works is to remove all the previous Changed Type steps in the query. Then using the locale works fine. For some reason doing it after a previous type change creates the errors
