Forum Discussion
Changing a variable amount of columns to type date
Hi,
I have a column with rows that contain date and email pairs as a string separated by a delimiter. The amount of date/email pairs varies. In this example I have two pairs but it also has to work for a variable amount of 3, 4, 5 or more entries.
In a first step I split the entries of the rows into seperate columns. This works, however the date columns are of type text and not of type date. I tried to convert them using Table.TransformColumnTypes() but I could not get it to work.
Any help would be appreciated.
let
Source = #table({"String"}, {{"10/26/2022,[email protected];11/02/2022,[email protected];"}}),
#"Number Of Columns to Split" = Table.AddColumn(Source, "Number of Columns Needed for Split", each if [String] <> null then List.Count(Text.PositionOfAny([String], {";", ","}, Occurrence.All)) else null),
DynamicCols = List.Transform(
{1..List.Max(#"Number Of Columns to Split"[Number of Columns Needed for Split])},
each "Entry" & Text.From(_)
),
Custom1 = #"Number Of Columns to Split",
#"Split Column by Delimiter" = Table.SplitColumn(Custom1, "String", Splitter.SplitTextByAnyDelimiter({";", ","}, QuoteStyle.Csv), DynamicCols),
#"Rename Column Names" = Table.TransformColumnNames(#"Split Column by Delimiter", (columnName as text) as text => if Number.IsEven(try Number.FromText(Text.End((columnName as text), 1)) otherwise 0) then Text.Replace(columnName, "Entry", "Name") else Text.Replace(columnName, "Entry", "Date"))
in
#"Rename Column Names"
I found a solution. Maybe not the most beautiful, but it works.
let Source = #table({"String"}, {{"26/10/2022,[email protected];11/02/2022,[email protected];"}}), #"Number Of Columns to Split" = Table.AddColumn(Source, "Number of Columns Needed for Split", each if [String] <> null then List.Count(Text.PositionOfAny([String], {";", ","}, Occurrence.All)) else null), DynamicCols = List.Transform( {1..List.Max(#"Number Of Columns to Split"[Number of Columns Needed for Split])}, each "Entry" & Text.From(_) ), Custom1 = #"Number Of Columns to Split", #"Split Column by Delimiter" = Table.SplitColumn(Custom1, "String", Splitter.SplitTextByAnyDelimiter({";", ","}, QuoteStyle.Csv), DynamicCols), #"Rename Column Names" = Table.TransformColumnNames(#"Split Column by Delimiter", (columnName as text) as text => if Number.IsEven(try Number.FromText(Text.End((columnName as text), 1)) otherwise 0) then Text.Replace(columnName, "Entry", "Name") else Text.Replace(columnName, "Entry", "Date")), columns = Table.ColumnNames(#"Rename Column Names"), dateColumns = List.Select(columns, each Text.StartsWith(_, "Date")), transferTypeToDate = Table.TransformColumnTypes(#"Rename Column Names", List.Transform(dateColumns, each {_, type date})) in #"transferTypeToDate"
3 Replies
Hi jk100 ,
The preferred solution is to use "Detect Data Type" or change type as Date manually under Transform.
If it is unsuccessful, your date format is different from your local date format. Then try the second solution below.
Right-click the date column and click "Using Locale...".
Select the Locale you need, and there are sample input values for reference.
Click OK and the type will be date.
About more details, you can refer to
Data types in Power Query - Power Query | Microsoft Learn
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Stephen Tao,
thank you for your response.
The power query should run automatically so I cannot manually trigger "Detect Data Type". Another issue is that I never know how many Date columns will be created in the previous steps.
My idea was to create a list of the column names using "List.Transform()" - as in the #"Rename Column Names" step - and then use "Table.TransformColumnTypes()" to change the column type if the column names matches a certain string pattern. Unfortunately, I did not get it to work yet:let Source = #table({"String"}, {{"10/26/2022,[email protected];11/02/2022,[email protected];"}}), #"Number Of Columns to Split" = Table.AddColumn(Source, "Number of Columns Needed for Split", each if [String] <> null then List.Count(Text.PositionOfAny([String], {";", ","}, Occurrence.All)) else null), DynamicCols = List.Transform( {1..List.Max(#"Number Of Columns to Split"[Number of Columns Needed for Split])}, each "Entry" & Text.From(_) ), Custom1 = #"Number Of Columns to Split", #"Split Column by Delimiter" = Table.SplitColumn(Custom1, "String", Splitter.SplitTextByAnyDelimiter({";", ","}, QuoteStyle.Csv), DynamicCols), #"Rename Column Names" = Table.TransformColumnNames(#"Split Column by Delimiter", (columnName as text) as text => if Number.IsEven(try Number.FromText(Text.End((columnName as text), 1)) otherwise 0) then Text.Replace(columnName, "Entry", "Name") else Text.Replace(columnName, "Entry", "Date")), #"Changed Type" = Table.TransformColumnTypes(#"Rename Column Names", List.Transform(Table.ColumnNames(#"Rename Column Names"), (column) => if Text.StartsWith(column as text, "Date") then type date else type any)) in #"Changed Type"
I found a solution. Maybe not the most beautiful, but it works.
let Source = #table({"String"}, {{"26/10/2022,[email protected];11/02/2022,[email protected];"}}), #"Number Of Columns to Split" = Table.AddColumn(Source, "Number of Columns Needed for Split", each if [String] <> null then List.Count(Text.PositionOfAny([String], {";", ","}, Occurrence.All)) else null), DynamicCols = List.Transform( {1..List.Max(#"Number Of Columns to Split"[Number of Columns Needed for Split])}, each "Entry" & Text.From(_) ), Custom1 = #"Number Of Columns to Split", #"Split Column by Delimiter" = Table.SplitColumn(Custom1, "String", Splitter.SplitTextByAnyDelimiter({";", ","}, QuoteStyle.Csv), DynamicCols), #"Rename Column Names" = Table.TransformColumnNames(#"Split Column by Delimiter", (columnName as text) as text => if Number.IsEven(try Number.FromText(Text.End((columnName as text), 1)) otherwise 0) then Text.Replace(columnName, "Entry", "Name") else Text.Replace(columnName, "Entry", "Date")), columns = Table.ColumnNames(#"Rename Column Names"), dateColumns = List.Select(columns, each Text.StartsWith(_, "Date")), transferTypeToDate = Table.TransformColumnTypes(#"Rename Column Names", List.Transform(dateColumns, each {_, type date})) in #"transferTypeToDate"
