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
aktripathi2506
Helper IV
Helper IV

Error with importing date columns

Hi,

 

This is a very common error I am getting everytime. I am importing data from excel to power BI. WHenever excel has a date column it gives the error in power BI query mode.

When I click on the error it gives me following message:

 

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
42534

 

I check the excel, the data type is date still it is not taking it correct. I get this error most of the time.

 

Please advise.

6 REPLIES 6
v-yuezhe-msft
Employee
Employee

Hi @aktripathi2506,

It seems there are some issues with the Date column in Excel file, I would recommend you follow the instructions in this similar blog to verify that if these Date data are defined with Date format or Text format.

In addition, the date data type/format is controlled by the Locale Setting in Power BI Desktop, change the Locale setting of Desktop to match the source date format following the instructions in this similar thread.

By the way, is your excel saved as .xlsx or .xlsb file?

Thanks,
Lydia Zhang

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

Hi @v-yuezhe-msft

thank you for the reply, I checked all the bolg and post you mentioned and confirmed everything is correct the way it should be.

 

Finally I tried something random and it worked for now but I am not sure if this is a permanent solution. I selected the entire column with header and change the data type to date and then it worked.

 

My excel file is saved as xls, the olderversion so I am using 32 bit power BI. Does power BI support xlsb? because I was in impression that 32 bit support only xls and 64 bit supports xlsx and xlsm.

 

 

Hi @aktripathi2506,

Glad to hear that the issue is resolved.

Based on my test, when using Power BI Desktop to conenct to .xlsb file, something doesn't work as expected such as Date. Currently, we can use Power BI Desktop(32bit and 64 bit) to connect to .XLS, .XLSX and .XLSM files.

Thanks,
Lydia Zhang

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

I was having the same problem - Excel file has whole date column formatted date mm/dd/yy, but importing to Power BI only resulted in non-date format you'd experienced.   What fixed this for me was converting the file from .xls to .xlsx... not sure why it made the difference, but importing into PBI now automatically has the correct date format without any need for intervention.   Just thought I'd share in case it can help others!

KGrice
Memorable Member
Memorable Member

Maybe your date column in Excel is formatted like a date, but isn't really a date? If it's text that looks like a date you and I can recognize, but isn't really a date that Excel/Power BI recognize as a date, that could be it. What happens when you change the format in Excel to General? If it's really a date, the cell should now show a number.

 

If that doesn't help, can you provide a screenshot of the data you have in Excel?

HI,

 

No I checked it, the data type was date only. When I change it to text I get numbers.

I tried something random and it worked for now but I am not sure if this is a permanent solution. I selected the entire column with header and change the data type to date and then it worked.

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.