Forum Discussion

aktripathi2506's avatar
aktripathi2506
Helper IV
10 years ago

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 th...
Need Help
Anonymous's avatar
Anonymous
Not applicable
10 years ago

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's avatar
aktripathi2506
Helper IV
10 years ago

Hi Anonymous

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.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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's avatar
      MTracy
      Advocate I
      8 years ago

      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!