Forum Discussion
jk100
3 years agoFrequent Visitor
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 ...
- 3 years ago
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"
jk100
3 years agoFrequent Visitor
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"