cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alkhos
Frequent Visitor

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

Hello,

 

I am working with a dataset retrieved as a CSV file through web.

I have a time field on the dataset with values similar to below:

         10/31/2016 4:05:00 PM

 

When data is fed to PBI desktop, it is automatically detected as "text".

When I attempt to change its type to Date/Time, I get the following error:

 

       DataFormat.Error: We couldn't parse the input provided as a DateTime value.
       Details:
       10/31/2016 6:25:00 PM

 

I have checked the source, all values are similarly formatted ( no add values in the time field )

I have also used PromoteHeaders to prmote the first row ( which contains header to the header field ) before trying to change the type.

 

The exact step that fails uses this DAX code:

 

         #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"time", type datetime}}),

 

Is there anything that I am doing wrong? or do I need any further pre-processing on this time field to be able to properly parse it?

 

Thanks

Ali

 

1 ACCEPTED SOLUTION
BhaveshPatel
Community Champion
Community Champion

After playing with your text file for 15 mins, I have found that there are some extra space at the front of your date column. Can you please remove those extra characters. 

 

Steps to follow afterwards:

1. Split the column by 11 characters. This will separate Date and Time

2. Split the Date column by "/" Delimiter

3. Change the data type of the newly created columns to Whole number

4. Bring the Day column first by dragging, Month column second and Year column last

5. Select all these three collumns and go to Add column Tab, Select Merge Columns and in Merge dialog, Select "/"Separator.

6. Change the data type of the merged column to Date.

 

So many steps .....

 

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

11 REPLIES 11
IWasTheL10N
New Member

Just a Note:  I had the same error when I tried to format and American Date (MMDDYY).  If you're in Europe (Ireland anyway) you need to arrange the date in DD/MM/YY.  

RaviTeja_M
New Member

Steps to be followed  to resolve the Date Format Error :

 

1. When  a CSV file is loaded in Power BI Desktop  by default it detects the 'date/time' field  as 'text' so when we try to convert we get an error.

 

Date_Format_Error.png

 

2. Next Right click on the Field/Column  you want to convert into a Date Field and navigate to 'Change Type' .

 

Change_type.png

 

3. Next Click on 'Using Locale' option under the 'Change Type' dropdown.

 

Using_locale.png

 

4. Change the 'Data type' to 'Date' in the dialog box of 'Using Locale'  as by default it takes it as 'text'.

 

date_as_datatype.png

5. Click on the drop down of 'locale'.

 

Locale_Drop_down.png

6. Then Select an 'Option' from drop down for which your Date field  'Format' matches exactly and Click on 'OK' . 

 

Format.png

7. Above steps will resolve the issue and same solution goes well for Date/Time column conversion also.

 

Superb! Works perfectly

@RaviTeja_M your hint solved my issue, thak you!

kingrob01
Frequent Visitor

How would I fix this error? It appears after I perform a data refresh.

DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
18 Days 15 Hours 59 Minutes

Greg_Deckler
Super User IV
Super User IV

That's actually Power Query "M" code, not DAX code. Definite difference. I tried this with your data and it seemed OK with what you presented. In Power Query I was able to convert it to datatime type. I would try cleaning and trimming your data column to see if it removes any weird characters or odd trailing spaces and such. You could also try the transformation in your data model (after import from Power Query) and see if it works there.

 

Any chance you can post the link to the actual CSV file?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @BhaveshPatel, which delimiter did you mean to use for splitting and which for joining?

 

Thanks @Greg_Deckler,

 

Thanks for clarification regarding M and DAX 🙂

 

Below is a sample of the data ( with some ananymization and keeping only two rows:

 

https://1drv.ms/t/s!AgdruS6e6l9tl3ojoTP5-737sLTI

 

I seem to have the same issue with importing this file as CSV as well ( similar to using the web source )

 

 

BhaveshPatel
Community Champion
Community Champion

After playing with your text file for 15 mins, I have found that there are some extra space at the front of your date column. Can you please remove those extra characters. 

 

Steps to follow afterwards:

1. Split the column by 11 characters. This will separate Date and Time

2. Split the Date column by "/" Delimiter

3. Change the data type of the newly created columns to Whole number

4. Bring the Day column first by dragging, Month column second and Year column last

5. Select all these three collumns and go to Add column Tab, Select Merge Columns and in Merge dialog, Select "/"Separator.

6. Change the data type of the merged column to Date.

 

So many steps .....

 

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

Anonymous
Not applicable

I had the same issie but half of the colum was error other half is ok . 

This is what I have done 

 

1. Split the colum by Delimiter Custom" / "Each Occurrence of dilimeters 

2. Carefully look at the colums and found that error is caused due to date is recoded without leading zero eg ; 8th is recorede as 8 but it needed to be 08 

3. Then I Inserted leading zero - chage the data type to text  then Incert Custom Colum Text.PadStart([Date]),2,”0″)

 There we go you will see the leading zero 

4. Then go to merge all sorted . 

 

 

Thanks 

Hi,

Could you please help me in fixing the error.

 

let
DayPassedInYear = (x) =>
let
MonthList=List.Numbers(1,Date.Month(DateTime.FromText(x))-1),
Year=Date.Year(DateTime.FromText(x)),
DaysInMonthList=List.Transform(MonthList,each Date.DaysInMonth(DateTime.FromText(Text.From(Year)&"-"&Text.From(_)&"01")))
in
Date.Day(DateTime.FromText(x))+List.Sum(DaysInMonthList)
in
DayPassedInYear("7/10/2014")

 

 

Date expression error.png

 

Thanks

BhaveshPatel
Community Champion
Community Champion

Splitting the column and rejoining them using "&" operator would solve the problem. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors