Forum Discussion
DataFormat.Error: We couldn't parse the input provided as a DateTime value.
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 .....
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 .....
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
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
Thanks a lot. I could resolve the issue. The solution worked.
