Change random dates

[tgarthaffner]

I have a data set where there is a column with dates when a particular instance became ready for the next step. I want to convert any field in that column that has a date to read "INSTALL READY" instead. How can I accomplish this using power query. My conditional column and replace value attempts have failed. 

Some of the fields are also blank, and some have a zero as an entry. 

Thank you

[SundarRaj]

Hi @tgarthaffner ,
You can use the Table.TransformColumns along with the try function to change the DATES ONLY to "Install Ready" ad keep the rest as same. Thanks

Please feel free to reach out in case of any queries.

Best,

[Olufemi7]

Hello @tgarthaffner, 

You can do this in Power Query by checking whether the value in your column is a date and then replacing it with the text `"INSTALL READY"`. A simple way is to use a custom column or transform the column directly in the Advanced Editor.

Option 1 – Add a Custom Column

1. In Power Query, go to Add Column → Custom Column.
2. Use this formula (replace `ReadyDate` with your column name):

= if Value.Is([ReadyDate], type date)
then "INSTALL READY"
else [ReadyDate]

• If the value is a date → it becomes `"INSTALL READY"`.
• If it’s blank or `0` → it stays as-is.
• Any other values are preserved.

Option 2 – Transform the Column In Place

If you want to overwrite the existing column instead of creating a new one, open the Advanced Editor and wrap your step like this:

= Table.TransformColumns(Source, {
{"ReadyDate", each if Value.Is(_, type date) then "INSTALL READY" else _}
})

Microsoft Documentation

Microsoft’s official guide on replacing values in Power Query explains how the Replace Values feature works and why conditional replacements require custom logic:

https://learn.microsoft.com/en-us/power-query/replace-values

This way, every date entry will be replaced with `"INSTALL READY"`, while blanks and zeros remain untouched.

[v-echaithra]

Hi @tgarthaffner ,

Thank you @ronrsnfld , @MasonMA  for your inputs.

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help!

Thank you 

[ronrsnfld]

You can use the Table.ReplaceValue function, but you need to write custom M code in the Advanced Editor:

Source Data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31TcyMDJVitWJVjIAk2DC0Ejf2BAkYwLmmugbmoN4FggVSIpN4YbEAgA=", 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 any}}),
    
    #"Replace Dates" = Table.ReplaceValue(
        #"Changed Type",
        each [Date],
        "INSTALL READY",
        (x,y,z)=>if Value.Is(Date.From(y),type date) then z else y,
        {"Date"})
        
in
    #"Replace Dates"

Results

[MasonMA]

Hi, 

In Power Query, Add a Custom Column and update [Column] to your actual Column name, 

if Value.Is([Column], type date)
or Value.Is([Column], type datetime)
then "INSTALL READY"
else if [Column] = null or [Column] = 0
then null
else Text.From([Column])