Forum Discussion

Mars3442's avatar
Mars3442
Helper I
2 years ago
Solved

Replace only null value and 1 condition

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 :

Order = Arrive, the status = delivered

Without replace the cell that have been filled

 

My expectation table like this

 

But when I write the M code

= Table.ReplaceValue (#stepbefore, each [Status], each if [Status]=null and [Order qty]=[Arrive qty] then "Delivered", else[Status], Replacer.ReplaceValue,{"Status"})

 

There is nothing change

 

Please help me to solve this problem

 

Thank you..

 

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    Hello, Mars3442 try custom replacer function

    Table.ReplaceValue(
      stepbefore, 
      "", 
      each ([Order Qty] = [Arrive qty]) and ([Status] = null),
      (value, old, new) => if new then "Delivered" else value, 
      {"Status"}
)

1 Reply

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    Hello, Mars3442 try custom replacer function

    Table.ReplaceValue(
      stepbefore, 
      "", 
      each ([Order Qty] = [Arrive qty]) and ([Status] = null),
      (value, old, new) => if new then "Delivered" else value, 
      {"Status"}
)