Forum Discussion
Solved
Excel Date problem
Hello. I get a file every day with telephone statistics from our switchboard. It has the same format every time so in order to be able to merge the data, I want to use the 'Get Data/from File/from Folder'.
Everything works with the merge and I can chose 'Combine and Transform Data'. But when I click on the example file to check the data, the dates are all wrong.
The date format is ok though, but Excel mixes up Year, month and day.
Original date format in file
Above is the original file with the dates. Below is the converted dates.
Wrong date format
The day have become the year, and the year have become the day....
The file I recieve have been produced in Germany, and I am in Sweden with a slightly different way of writing dates, but I could live with both variants, as long as they are correct. Since I am using the 'Get data/from File/from Folder', I never get a chance to convert the dates before I merge the files, and after, they are wrong. Any help would be deeply appreciated.
spehe Your Swedish system doesn't recognise 02.05.22 as a date. Change the data type in the #"Changed Type" step for "Call Start Time" and "Call End Time" by "Using Locale..."
and then select Date/Time and choose "German (Germany)" as the locale.
That should fix it.
4 Replies
- Can you go into the Power Query Editor and show us the code for the query in the Advanced Editor?
- Here is the code from the Advanced Editor:
let
Source = Folder.Files("C:\Users\zschermans\OneDrive - STIHL Group\UC\Statistics\Test reports\x"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Call Start Time", type text}, {"Column2", type any}, {"Call End Time", type text}, {"Contact Disposition", Int64.Type}, {"Originator DN (Calling Number)", Int64.Type}, {"Destination DN", Int64.Type}, {"Called Number", Int64.Type}, {"CSQ Names", type text}, {"Queue Time", type datetime}, {"Agent Name", type text}, {"Ring Time", type datetime}, {"Talk Time", type datetime}, {"Work Time", type datetime}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Call Start Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Call Start Time.1", "Call Start Time.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Originator DN (Calling Number)", type text}, {"Called Number", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column2"})
in
#"Removed Columns"spehe Your Swedish system doesn't recognise 02.05.22 as a date. Change the data type in the #"Changed Type" step for "Call Start Time" and "Call End Time" by "Using Locale..."
and then select Date/Time and choose "German (Germany)" as the locale.
That should fix it.
