Forum Discussion
Dataflow DataFormat.Error: We couldn't parse the input provided as a DateTime valu
Hi higgy7
Try editing the "Changed column type" step to add the locale information at the end, as highlighted in below code. This should work.
let
Source = Folder.Files("\Folder Path\PowerBI Datasources"),
Navigation = Source{[#"Folder Path" = "\Folder Path\PowerBI Datasources", Name = "DataName.csv"]}[Content],
#"Imported CSV" = Csv.Document(Navigation, [Delimiter = ",", Columns = 9, Encoding = 65001, QuoteStyle = QuoteStyle.None]),
#"Promoted headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars = true]),
#"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"DateTime", type datetime}, {"Name", type text}, {"Country", type text}, {"Location", type text}, {"Type", type text}, {"Remarks", type text}, {"Latitude", type number}, {"Longitude", type number}, {"Precipitation_mm", type number}}, "en-GB"),
#"Duplicated column" = Table.DuplicateColumn(#"Changed column type", "DateTime", "DateTime - Copy"),
#"Changed column type 1" = Table.TransformColumnTypes(#"Duplicated column", {{"DateTime", type date}, {"DateTime - Copy", type time}}),
#"Sorted rows" = Table.Sort(#"Changed column type 1", {{"DateTime", Order.Descending}})
in
#"Sorted rows"Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Hi higgy7
Try editing the "Changed column type" step to add the locale information at the end, as highlighted in below code. This should work.
let
Source = Folder.Files("\Folder Path\PowerBI Datasources"),
Navigation = Source{[#"Folder Path" = "\Folder Path\PowerBI Datasources", Name = "DataName.csv"]}[Content],
#"Imported CSV" = Csv.Document(Navigation, [Delimiter = ",", Columns = 9, Encoding = 65001, QuoteStyle = QuoteStyle.None]),
#"Promoted headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars = true]),
#"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"DateTime", type datetime}, {"Name", type text}, {"Country", type text}, {"Location", type text}, {"Type", type text}, {"Remarks", type text}, {"Latitude", type number}, {"Longitude", type number}, {"Precipitation_mm", type number}}, "en-GB"),
#"Duplicated column" = Table.DuplicateColumn(#"Changed column type", "DateTime", "DateTime - Copy"),
#"Changed column type 1" = Table.TransformColumnTypes(#"Duplicated column", {{"DateTime", type date}, {"DateTime - Copy", type time}}),
#"Sorted rows" = Table.Sort(#"Changed column type 1", {{"DateTime", Order.Descending}})
in
#"Sorted rows"
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Thank you very much, this has worked!
Would you have a link to any documentation on this or a link to any further information as i was not aware of this.
Actually this locale info can be added to the code by "Change type Using Locale" automatically. However as this error already happened, adding a subsequent new step to change its type using locale won't fix the existing error in previous step. So the solution is to edit the step where the error occurs. You can also remove that error "Changed column type" step from the Applied Steps Pane, then change data types for columns manually using locale. The key is to change the data type correctly at the initial first "changed type" step.
Thanks for this info that makes sense.
Can i just ask is there a setting in the Power BI service that needs changed by our admin so that the locale is en-GB rather than US? I assume this is why it is looking for US formatted dates rather than UK.
Hi higgy7
You can change the language preference for your account in power bi service. This will make a difference.
Based on my test, I don't have en-gb choice in above setting's language list, so I change my browser's preferred language to en-gb. When adding a table in a dataflow, it detects the dates as en-gb and adds "en-GB" to "changed type" step automatically.
But if I removed "en-GB" from the code, it brought errors for some dates. So I guess if the code doesn't have a locale info, it perhaps uses en-US as default. But I don't find any documentation to support my guess so it's just a guess😅 You need to check whether it detects the locale correctly and correct it if needed.
Best Regards,
Jing