Forum Discussion
spehe
Oct 05, 2022Copper Contributor
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 Fo...
- Oct 06, 2022
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.
spehe
Oct 06, 2022Copper Contributor
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"
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"
Riny_van_Eekelen
Oct 06, 2022Platinum Contributor
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.
- speheOct 07, 2022Copper ContributorSpot on! That fixed my problem, thanks a lot!