Forum Discussion
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" 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
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])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
7 Replies
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
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,
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])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 youHello 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 PlaceIf 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.
Hi tgarthaffner ,
Thank you SundarRaj , Olufemi7 for your inputs.
May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.
Thank you.
Hi tgarthaffner ,
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
