Forum Discussion

Mars3442's avatar
Mars3442
Helper I
2 years ago
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 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..

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

5 Replies

  • ChielFaber's avatar
    ChielFaber
    Super User
    2 years ago

    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's avatar
      Mars3442
      Helper I
      2 years ago

      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?

      • AlienSx's avatar
        AlienSx
        Super User
        2 years ago

        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.

  • ChielFaber's avatar
    ChielFaber
    Super User
    2 years ago

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