Showing results for 
Search instead for 
Did you mean: 
Helper IV
Helper IV

Trouble with importing date into power BI



I am taking data from an excel file. It has a several columns with having 2 columns as ‘date’. Now when I connect it to power BI, sometimes it works and sometimes it doesn’t.

Sometimes it reads the date data type correctly, and sometimes when I refresh the power query mode, I am getting date as error in query mode:


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



Regular Visitor


I have the same or similar issue, this is what I tried

  • using excel
  • entire columns is dates - I even format-painted from a sheet that Power BI recognized as dates
  • deleted bottom rows in case blanks were showing
  • did a text-to-column trick for making sure the m/d/y format was right
  • Power BI still didn't recognize the number as a date, would return errors if I tried to change the type


Temporary workaround was to copy the data and paste in a new workbook and it worked fine.  Will post a follow up if I figure out why it was behaving that way in the first place. 



You can try and import the column as text and then add a calculated column that converts it to a date- and then filter that column on error values.






Thank you for the reply. I am still not getting how to do that. THe excel sheet in which I have the date column i can not change that as other people use that file (and they probaly will not like it if i change it to text, it should look like date only).  Whatever I can do is on power BI side.


Can you please give a detail reply. If you can give the formula as well then that will help.


Thank you.



When you first created the import of the excel file the column with dates properly only contained valid dates and power query will the guess and set the column data type to date




notice the calendar symbol at the left of the column name.


You should check the steps  in the query and then modify the data type to text




and then you can use the add column to parse the date


Untitled picture.png


and then you have a column that you can filter by error values




or you can replace error values with a specific date




Hope this can help you further




At the very first step, in power BI, in my case all the data was coming as error. When I changed it to text than I got the numbers but when I parsed I was getting errors.  I tried the way you explained. It didn't work.


And also I dont want to manually replcae error with the values. I want it to be automated. Solution for always so I dont have to check it again and again.



@aktripathi2506 It sounds like there are values in your date column that are not actually dates. What @donsvensen outlines is a process to import your values as text. Then create a step to "clean" those errors, and have a seperate column with actual date values. This process of steps will happen everytime you refresh the data set from your excel file, you don't have to do this over and over each time. You are defining a one time process to cleanse the data in the Power BI Desktop file...

What error are you getting when you try to apply these steps?

Looking for more Power BI tips, tricks & tools? Check out the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Agree with @Seth_C_Bauer - you have to build in a validation/cleansing of the data in order to avoid these errors.


And I would start with a manual check of the data in the excel file to see why the dates aren't valid





Helpful resources

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors