Forum Discussion

higgy7's avatar
higgy7
Advocate II
2 years ago
Solved

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

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
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      higgy7
      Advocate II
      2 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.