Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

16 REPLIES 16
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
Employee
Employee

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?

 

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.