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

Re: Trouble with importing date into power BI

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

aktripathi2506 Helper IV
Helper IV

Re: Trouble with importing date into power BI

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.

Re: Trouble with importing date into power BI

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

aktripathi2506 Helper IV
Helper IV

Re: Trouble with importing date into power BI

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.

Super User III
Super User III

Re: Trouble with importing date into power BI

@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

Re: Trouble with importing date into power BI

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

 

 

visualizlucidly
Regular Visitor

Re: Trouble with importing date into power BI

 

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. 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors