Forum Discussion
Solved
Replace null value with value from other column
Hi everyone, I want to ask a help for solving my problem I have a table like this I need to replace the null value with these conditional : Delay Reason= not null, the status = Delay Rea...
Hi Mars3442
You can try the following code
=Table.ReplaceValue(the last step name,e.g #"Changed Type",each [Status],each if [#"Source.Delay/Late Reason"]<>"" then [#"Source.Delay/Late Reason"] else [Status] ,Replacer.ReplaceValue,{"Status"})Output
Or you can put the following code to advanced editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcknNySxLLUpNUYrViVYKKUrMKy7IL0osyczPA8qCBWE0LkkkI2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source.Delay/Late Reason" = _t, Status = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source.Delay/Late Reason", type text}, {"Status", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Status],each if [#"Source.Delay/Late Reason"]<>"" then [#"Source.Delay/Late Reason"] else [Status] ,Replacer.ReplaceValue,{"Status"}) in #"Replaced Value"Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It still nothing change with my data
Actually my "delay reason" column name is "Source.Delay/Late Reason"
the M code error when I use that column name
what should I do?
Hi, Mars3442 try this
let
Source = table_like_this,
replace =
Table.ReplaceValue(
Source,
"",
each [#"Source.Delay/Late Reason"],
(value, old, new) => new ?? value,
{"Status"}
)
in
replace
Why don't you simply add new column to check if "Source.Delay/Late Reason" is null ? Then remove old Status column. That's easier and must be faster.