Possible bug when importing dates from xls in spanish locale (es-ES)

[eyesem]

I'm trying to import several .xls files (can't modify the source files). Dates in Excel are perfectly fine (formated with mmm-aa in spanish locale), but when importing dates from March I get an error. Do you think this might be a bug or am I doing something wrong? I'm running the latest Power BI Desktop version.

let
    Source = Folder.Files("C:\DataSets\test"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from test", each #"Transform File from test"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each Text.StartsWith([Source.Name], "HUBS")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows", {"Source.Name", "Transform File from test"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from test", Table.ColumnNames(#"Transform File from test"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",6),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Removed Top Rows1" = Table.Skip(#"Promoted Headers",2),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Top Rows1", each [Hub] <> null and [Hub] <> "" and not Text.Contains([Hub], "Curva") and not Text.Contains([Hub], "Tipo") and not Text.Contains([Hub], "Fecha") and not Text.Contains([Hub], "Hub") and not Text.Contains([Hub], "Fuente") and not Text.Contains([Hub], "Uds")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Hub", "Date"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}, "es-ES")
in
    #"Changed Type with Locale"

I couldn't attach files here, so here is a wetransfer link with .pbix and 2 excel sample files.

https://we.tl/t-P5KrtG4ToZ

Thank you! 

[v-yuezhe-msft]

@eyesem,

Thanks for sharing the files. However, everything works as expected in my Power BI Desktop(2.65.5313.841 ). The region of my computer is Spain.



Regards,
Lydia

[eyesem]

I have the exact same version and configuration! How is it possible? I keep getting error.

[v-yuezhe-msft]

@eyesem,

I didn't set the Locale for import option as below in Power BI Desktop, I directly change locale in Power BI Desktop query editor.



Regards,
Lydia