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.
Solved! Go to Solution.
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.
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
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.
Here is a link with sample data as requested in a csv file. Thanks in advance.
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:
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
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.
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.
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!
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.
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?
Can you please share the column with the dates to test it.
Upload to dropbox or one drive and share the link.
Click here to read more about the November 2022 updates!
Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.
This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.
Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.