Replace null value with value from other column

[Mars3442]

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 Reason value

Without replace the cell that have been filled

My expectation table like this

What M Code I should write to get my expectations?

Please help me to solve this problem

Thank you..

[ChielFaber]

Do you have a part of your m-code? Like the previous row and the correct names

[ChielFaber]

Try the following M-code. Add it in the advanced editor. Replace the text #"Previous_STEP" with the previous step name in the advanced editor.

Table.ReplaceValue(#"Previous_STEP", each [Status], each if [Delay Reason] <> null then [Delay Reason] else [Status],Replacer.ReplaceValue,{"Status"})

[Mars3442]

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?

[v-xinruzhu-msft · Accepted solution]

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.

[AlienSx]

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.