Forum Discussion
Changing a variable amount of columns to type date
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"
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"