Forum Discussion

olimilo's avatar
olimilo
Post Prodigy
5 years ago

Can I parameterize the Table.ReplaceValue command in a custom function?

I made the custom function below as I need to run the same function for 15+ columns, basically to replace colA with colB if it isn't null, otherwise to retain the value of colA:

 

 

let updateField = (dt as table, colA as text, colB as text) =>
    let Source =
        Table.ReplaceValue(dt, each colA, each if colB <> null then colB else colA, Replacer.ReplaceValue, {colA})
    in Source
in updateField

 

 

Unfortunately, when I invoked the custom function in my query, it didn't work. However, if I use the actual Table.ReplaceValue in a step, it works just fine. Is there some way I can make this function work? I figure what's causing it is because colA and colB are text parameters, but I don't think I can pass a column as a parameter.

Need Help

3 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    Hi olimilo ,

     

    I think you need to reference the Table.ReplaceValue() function once for each column you want to replace.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • LillyBeast's avatar
    LillyBeast
    Frequent Visitor
    3 years ago

    It needs an Evaluate.Expression wrap to allow running text as M-Code. See this example 
    Also, Table.ReplaceValue is simply passing three arguments - (in_value, look_for, replace_with) - to any function. Replacer.ReplaceValue is just a function that is used in the documentation, without really explaining how that works.
    So, try this:

     

    Evaluate.Expression(
   "Table.ReplaceValue(
      table
      , each _" & ColA & "
      , each _" & ColB & "
      , (value, value_in_ColA, value_in_ColB) => value_in_ColA ?? value_in_ColB
      , {" & ColA & "}
   )"
   ,[Table.ReplaceValue = Table.ReplaceValue]
)

     

    * -  `??` operator is M-Code's null-coalesce

  • LillyBeast's avatar
    LillyBeast
    Frequent Visitor
    1 year ago

    You would need to wrap that in an Evaluate.Expression to allow running text as M-Code. See this example 
    Also, Table.ReplaceValue is really just passing (in_value, look_for, replace_with) parameters to any function. You could write yours as:

    Table.ReplaceValue(
   table
   , each _ColA
   , each _ColB
   , (value, old, new) => old ?? new
   , {ColA}
)


    The `??` operator is M-Code's null-coalesce