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 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.

Need Help

6 Replies

  • KGrice's avatar
    KGrice
    Memorable Member
    10 years ago

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

      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.

  • 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