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

[higgy7]

Hi,

I am trying to create a dataflow but at the end I get the We couldn't parse the input provided as a DateTime value. Details Reason = DataFormat.Error Detail = 13/11/2023 02:00:00 error. 

The exact same steps work perfectly in Power BI Desktop but the date time field causes the error in the Power BI Service. The work around is to keep the date and time fields as text and then transform them in PowerQuery in desktop. I feel this is a bug as it works in desktop but not in service.

My steps are as follows: 

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}}),
#"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"

Any help would be greatly appreciated. I have tried changing the column type to Using Locale and Date/Time/Zone but neither of these make any difference. Note the DateTime is in UK format DD/MM/YYYY HH:MM:SS e.g. 28/11/2023 19:00:00

[v-jingzhan-msft · Accepted solution]

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!

[higgy7]

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.

[v-jingzhan-msft]

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. 

[higgy7]

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.

[v-jingzhan-msft]

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