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.
I have the same or similar issue, this is what I tried
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.
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.
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
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?
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
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps