Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Thank you!
@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
I have the exact same version and configuration! How is it possible? I keep getting error.
@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