Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
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.
Details:
41548
I was having this trouble just now and what appeared to be my source of trouble was importing from a .XLS file.
That was giving a date-parsing error. Once I swithced the source to .XLSX it rectified itself.
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.
Hi
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.
br
Erik
Hi,
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.
Hi
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
Br
Erik
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.
Thanks.
@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
/Erik
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |