Date Columns recognized as Text not "Date"

[Anonymous]

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.

[chandni90]

I have a column created date and successfully changed it to 'Date' from 'Text'. However, today it started throwing an error.
I tried changing the type with locale (English-United States) but it still gives me the error. Please help.

[Aamir_khan]

what is the solution ?

[fambow]

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

[v-yulgu-msft]

Hi @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 Gu

[Anonymous]

@Vvelarde

@v-yulgu-msft

Here 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

[myndworkz]

Thank you, I spent at least an hour doing this before I realised i had to make the 'locale' the same as the origin format, not the local format (English American not English UK). This answer should ba at the top of 'solved'. I may not have scrolled down to it.

[Anonymous]

@Vvelarde

Theanks for the feedback, I wish I had the same success.  After following the steps I receive a series of messages about not being able to parse and Error detection.  See screenshots below.

[Vvelarde · Accepted solution]

@Anonymous

The first row is your header? ..before to apply this you have to "Use first row as header"

[Anonymous]

Actually, once I promoted the first row as a header, that enabled me to change the column to a date without adjusting the locale. Nonetheless @Vvelarde you have again save the day.  Thanks for all the help!

[Vvelarde]

@Anonymous

Maybe is an error because the format of the date.

What format have?

[Anonymous]

The data in the column visually is 2/5/2017 as an example.  I can't however find a way to have Power BI recognize it as a date.

[Phil_Seamark]

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.

[Anonymous]

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?

[aditian8]

First convert it to number from text format & On top of that convert to Date. Boom!

[Vvelarde]

@Anonymous

Can you please share the column with the dates to test it.

Upload to dropbox or one drive and share the link.