Forum Discussion
DataFormat.Error: We couldn't parse the input provided as a DateTime value
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
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.
11 Replies
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.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!This worked for me -Thank u so much 🙂
Found your response after searching stuff for an hour!!
I encountered the same issue when I copied a query from a power bi report to a dataflow. Turned out I had none data values in a couple of my 40.000+ lines. I substituted "error" values with null. This solved my issue.
This solution worked for me. 🙂 Thanks!
I was having the same issue with a new column I added and this fixed it. Thanks!
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!
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.
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).
