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

[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!