Forum Discussion

tgarthaffner's avatar
tgarthaffner
New Member
7 months ago
Solved

Change random dates

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" inst...
Need Help
  • MasonMA's avatar
    MasonMA
    7 months ago

    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])

     

  • ronrsnfld's avatar
    ronrsnfld
    7 months ago

    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

     

     

Olufemi7's avatar
Olufemi7
Icon for Super User rankSuper User
7 months ago

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.