cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
irnm8dn
Post Prodigy
Post Prodigy

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:

 

DT.PNG

 

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.

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@irnm8dn

 

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




Lima - Peru

View solution in original post

15 REPLIES 15
chandni90
Frequent Visitor

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.
error.PNG

error.PNG

error.PNG

error.PNG

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

v-yulgu-msft
Microsoft
Microsoft

Hi @irnm8dn,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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
Community Champion
Community Champion

@irnm8dn

 

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 QueryGo to Edit Query

 

2. Select the two columns, Right Click --Change Type - Using Locale

 

Step2.png

 

3. Select in Data Type: Date and Set the Locale (in your case you can use English - United States)

Step3.png

 

4. Ready Close & Apply

 

Step4.png

 




Lima - Peru

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.

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

 

Date Column 2.13.2017.PNG2.13.2017.PNG

Vvelarde
Community Champion
Community Champion

@irnm8dn

 

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




Lima - Peru

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
Community Champion
Community Champion

@irnm8dn

 

Maybe is an error because the format of the date.

 

What format have?




Lima - Peru

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.

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.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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?

 

Vvelarde
Community Champion
Community Champion

@irnm8dn

 

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

 

Upload to dropbox or one drive and share the link.




Lima - Peru

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.