Changing a variable amount of columns to type date

[jk100]

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@email.com;11/02/2022,email@email.com;"}}),
    #"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"

[jk100 · Accepted solution]

I found a solution. Maybe not the most beautiful, but it works.

let
    Source = #table({"String"}, {{"26/10/2022,email@email.com;11/02/2022,email@email.com;"}}),
    #"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"

 

[Anonymous]

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.           

[jk100]

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@email.com;11/02/2022,email@email.com;"}}),
    #"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"