Search code examples
exceloffice365powerqueryconditional-formatting

Split Text containing Date into columns

Problem: Excel has the ability to recognise dates for instance if you write 01-Jan-2021 excel will convert this to 01/01/2021. Is it possible using power query to somehow use this as a sort of delimiter to split dates from Cells?

So far: Currently I am using a colon as a delimiter which works well but of course if this is accidently missed, then the date remains unsplit form the rest of the data.

Is it possible without this to automatically split dates and times etc from other data contained within a cell?

Alternative solution? Not sure if this could work but alternative is it possible to apply some conditional formatting so that all Dates are followed with a colon? The problem is suspect is that since the cell contains other text it wont be recognised as a date. This may also be an issue in general but if necessary using power query I could split the data as necessary

The image shows what I am trying to achieve

enter image description here

Solution

  • omitting any custom code, you can do this from the interface fairly simply

    Split by line-feed into rows

    duplicate column

    transform the data type on new column to date and then right click column and replace errors with null

    duplicate that 2nd column and right click it and fill down; you now have 3 columns

    filter 2nd column=null, and if you need to, original column <> blank

    remove the 2nd column

    its a 2 minute operation but code generated is:

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Column1", "Column1 - Copy"),
#"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"Column1 - Copy", type date}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type2", {{"Column1 - Copy", null}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Replaced Errors", "Column1 - Copy", "Column1 - Copy - Copy"),
#"Filled Down" = Table.FillDown(#"Duplicated Column1",{"Column1 - Copy - Copy"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([#"Column1 - Copy"] = null) and ([Column1] <> "")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1 - Copy"})
in #"Removed Columns"