cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aktripathi2506
Helper IV
Helper IV

Trouble with importing date into power BI

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

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

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

 

capture20160707205048329.png

 

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

 

capture20160707205602258.png

 

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

 

snip_20160707205903.png

 

or you can replace error values with a specific date

 

capture20160707210303778.png

 

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?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips 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

 

/Erik

 

 

Helpful resources

Announcements
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