Convert a text column to a date column

[Anonymous]

Hello all,

I have an excel file that I am reporting on. I have a column where the majority of entries are a date, however there are a handful of entries in the workbook that state some sort of text phrase such as "empty" or something else. Is there a way to ignore these text values and still change the column to date format? Thanks.

[v-jingzhang]

Hi @Anonymous 

How do you want to deal with the text phrases in this column? If you want to keep these text phrases as they are, the column could only be of Text data type. If you want to convert this column to Date data type, you will have errors for these text phrases and you could replace errors with null as @KNP has suggested. 

Below is an example. Also attached a .pbix file. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[KNP]

Hi @Anonymous,

Are you receiving an error currently when trying to convert to a date?

You could try this and see if it works.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjLQNzDVNzIwMlSK1QFyDfUNLBHc1NyCkkowy9BAH4ggErEA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [date = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"date", type date}}),
  #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"date", null}})
in
  #"Replaced Errors"

PBIX file attached also.