Forum Discussion

Mederic's avatar
Mederic
Post Patron
3 years ago
Solved

Message "DataFormatError"

Hello,
I have a range named "Tab" with dates in columns,
After some transformations, if I change the column "Attribute" to date type, I have a column with errors.

How to correct the last step to transform this column in date ?
Thanks in advance
Regards

 

 janv-23févr-23mars-23avr-23
Name94217478
A65573784
B54383029
C23239092
D68628457

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Tab"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}})
in
    #"Changed Type1"

 

 

  • Hi, Mederic . Feed Power Query with correct date representation in "MMM-yy" format. As far as I know in "fr-FR" locale the date must be like "janv.-23", not "janv-23" and so on.

2 Replies

  • Hi, Mederic . Feed Power Query with correct date representation in "MMM-yy" format. As far as I know in "fr-FR" locale the date must be like "janv.-23", not "janv-23" and so on.

  • Hello AlienSx ,

    Thank you for your message and sorry for the late reply.
    I hadn't thought about the regional settings, indeed the 2 steps below work and solve the problem
    thanks again

    Regards

    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Attribute", type datetime}}, "fr-FR"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Attribute", type date}})