Power Query - Table.ReplaceValue - Behavior ?

informer lets start from your last questions:

# 3 mandatory parameters for custom replacer: it's because Table.ReplaceValue passes all 3 to this function.

# assiciations: look at any standard PQ replacer function definition:

Replacer.ReplaceValue(value as any, old as any, new as any) as any

Arguments' values are passed in the same order. So that 

value : current value in one of columnsToSearch

old: oldValue in Table.ReplaceValue

new: newValue in Table.ReplaceValue

Keeping the above in mind try to imagine what's going on in your use cases: column by column, row by row...

This is an interesting question. It seems that you are very interested in the second example.

Before explaining this question, you should know that if a table function needs a function as a parameter, it usually passes the parameters in order, which I call implicit parameter passing. For example, the third parameter of the Table.AddColumn function needs to specify a function. During the execution of the Table.AddColumn function, it passes the table row (a record value) as a parameter to the third parameter (a function), and then calculates a result as the value of the new column.

The same is true for the Table.ReplaceValue function, which implicitly passes the value of each row of the column specified in the fifth parameter, the second parameter, and the third parameter to the fourth parameter (a function).The fourth argument (a function) computes a result based on the three provided arguments, which is then used as the new value to replace the original value.

In the second example, your result is a fixed value (newval), so it will always replace the original value in the table with newval（it corresponds to the third parameter）.

Here are some examples I learned before, maybe they can help you.

Table.ReplaceValue(
    #table({"A", "B"}, {{1, 2}, {11, 11}, {101, 102}}), 
    11, 
    666, 
    Replacer.ReplaceValue, 
    {"A"}
)

Table.ReplaceValue(
    #table({"A", "B"}, {{1, "take"}, {11, "make"}, {111, "like"}}), 
    "ak", 
    "****", 
    Replacer.ReplaceText, 
    {"B"}
)

Table.ReplaceValue(
    #table({"A", "B"}, {{1, 2}, {11, 11}, {101, 102}}), 
    888, 
    666, 
    (x, y, z) => if x = 11 then y else z, 
	{"A"}
)

Table.ReplaceValue(
    #table({"A", "B"}, {{1, 2}, {11, 11}, {101, 102}}), 
    null, 
    null, 
    (x, y, z) => if x = 11 then x * 100 else x / 100, 
    {"A"}
)

Table.ReplaceValue(
    #table({"A", "B"}, {{1, 2}, {11, 11}, {101, 102}}), 
    each [B], 
    666, 
    (x, y, z) => if y = 102 then 777 else x, 
    {"A"}
)

Your second example has too many parameters. If you specify the replacer function then the 

each [kidhome],   each 2

part is ignored.

Hi informer, check Rick's article.

Hi informer,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are trying to use "Table.ReplaceValue" function but in two different ways. As ronrsnfld, lbendlin, slorin and ZhangKun provided with responses, were you able to solve your issue? If your issue is solved then kindly mark the helpful response as solution and if you still have queries left then kindly mention your primary queries.

I would also take a moment to thank ZhangKun, lbendlin, AntrikshSharma and AlienSx, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.