- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 .....
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 .....
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for your previous explanation. I'm encountering a similar issue with my Power BI project that's connected to SharePoint. I rely on SharePoint to feed data into my dashboard.
I've successfully created my dashboard, but when attempting to upload an Excel file with the same template used previously in SharePoint and then refreshing my Power BI dashboard through Transform Data > Refresh, I encountered the following error: "DataFormat.Error: We couldn't parse the input provided as a Date value."
I double checked that the date data type in the new file matches the one in the file I initially uploaded when creating the project. Should I replicate the steps I took in Power BI previously to integrate the new Excel files into my existing project?
Thanks in advance,
Rawan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Go to the Query Editor (right-click on the table in 'Fields' on the right-side, and select 'Edit Query').
Find the column with the date
Right click on column header and change type to Text
'Apply and Close' the query editor (left side top you should see this)
Now on the left side you should click on 'Data' and then in the 'Fields' on the right side click on the relevant table
Now go to the column which must be the date
Select the column by pressing on column header
in the 'Column Tools', change 'Data Type' to Date
Change 'Format' to your preferred date format
PowerBI is a *****
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello, @BhaveshPatel is there any other and proper solution that exists for that because each time this occurs when I import a data column it produces errors when the date day exceeds than 12.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I did the split like you suggested by 11 characters, and that was all I needed - it automatically picked up the new column as a date
My date delimiter was a hyphen, not a forward slash
Thanks for the help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I'm getting same error.
DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
1/13/1997
I tried your solution as well others but still I'm getting same error again and again.
Please can you help to figure out on my error.
https://drive.google.com/file/d/1xpgI6JB7mzgqLi5eTULSmP5zQOIZF116/view?usp=sharing
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks a lot. I could resolve the issue. The solution worked.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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")
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Splitting the column and rejoining them using "&" operator would solve the problem.
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
03-26-2024 05:14 AM | |||
08-20-2024 01:49 PM | |||
12-11-2022 10:44 AM | |||
Anonymous
| 11-17-2021 12:26 PM | ||
Anonymous
| 03-19-2022 02:28 PM |
User | Count |
---|---|
126 | |
81 | |
59 | |
57 | |
43 |
User | Count |
---|---|
183 | |
111 | |
82 | |
66 | |
51 |