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

[rlymer]

Hi,

I have a Dataflow that has been working for nearly a year now, but is now throwing the error "DataFormat.Error: We couldn't parse the input provided as a DateTime value.

Details: 2021-10-21T11:57:06+13:00"

I'm guessing the service that I am pulling the data from has changed how they are displaying it. How can I change the above format to a DateTime as this was working previously?

Thanks

[kjrodriguez]

Hi everyone!!

After trying the solution found in this post...finally for me I found another solution for this issue.

Go to SharePoint settings (YourSharePointSite/_layouts/15/settings.aspx). find Regional Settings and change region there to appropriate one (with the same date format).

reference: types - We couldn't parse the input provided as a DateTime value (from SharePoint to Power BI) - Sta...

[KAI_TGOAT]

I see there are other responses provided that have worked for you; but just in case it doesn't work for someone else, like me haha, here's another method you can try:
When you import your data (mine was imported from excel), you'll initially have to change your date columns from text to whole number. Then change the data type from whole number to date or datetime. This is the only thing that worked for me. Hope this helps!

[Prakharlive]

1. Try the following on the "Transform Data"

Transform > Text Column > Extract > "Put the number of characters to be extracted."

2. Convert the new text into Date and Time format.

Solves the problem pretty easily.

[Julieann]

Suggested solution: That is a DateTimeZone format. You will need to use DateTimeZone for the field type, then click on Change Type again and change to DateTime and make sure it adds a step, not replaces.

The suggested solution to fix this problem seems to be not working on my dataset. 

Are there alternation options to possibly fix this error?? Thank you!

[SHeRA42]

I was having the same issue with a new column I added and this fixed it. Thanks!

[edhans · Accepted solution]

That is a DateTimeZone format. You will need to use DateTimeZone for the field type, then click on Change Type again and change to DateTime and make sure it adds a step, not replaces.

You will have two change type steps. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1NNA1MgwxNLQyNbcyMNM2NLYyMFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetimezone}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type datetime}})
in
    #"Changed Type1"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[rlymer]

Hi @edhans,

Thank you for your response.

Simply changing them to datetimezone instead of datetime seemed to solve this issue.

Thanks!