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

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.

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.

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!

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.

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 *****

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