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.
flexyourdata
Oct 05, 2022Iron Contributor
Can you go into the Power Query Editor and show us the code for the query in the Advanced Editor?
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_EekelenOct 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!