Error with importing date columns

[aktripathi2506]

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.

[v-yuezhe-msft]

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

[aktripathi2506]

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.

[v-yuezhe-msft]

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

[MTracy]

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]

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?

[aktripathi2506]

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.