Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mederic
Helper V
Helper V

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"

 

 

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

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.

View solution in original post

2 REPLIES 2
Mederic
Helper V
Helper V

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}})

 

AlienSx
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors