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.
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 .....
I faced the same issue. I went back & checked my data sets, I noticed that the date columns were different in 2 of the 6 files.
One needs to double check the data source which is being appended.
In Power Query, go to transform in the ribbon and then Date>Parse
DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
7/18/1996
Thanks. I could resolve the issue. The solution worked.
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.
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.
2. Next Right click on the Field/Column you want to convert into a Date Field and navigate to 'Change Type' .
3. Next Click on 'Using Locale' option under the 'Change Type' dropdown.
4. Change the 'Data type' to 'Date' in the dialog box of 'Using Locale' as by default it takes it as 'text'.
5. Click on the drop down of 'locale'.
6. Then Select an 'Option' from drop down for which your Date field 'Format' matches exactly and Click on 'OK' .
7. Above steps will resolve the issue and same solution goes well for Date/Time column conversion also.
These steps were really helpful. thanks
Thank you! This solution worked for me 😀
this solution worked for me, thanks a lot for sharing your knowledge
Superb! Works perfectly
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
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?
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 )
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 .....
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 *****
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.
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!
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.
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
User | Count |
---|---|
215 | |
55 | |
47 | |
44 | |
43 |
User | Count |
---|---|
294 | |
207 | |
75 | |
75 | |
68 |