Power Query - Table.ReplaceValue - Behavior ?

[informer]

Hi,

My question is very basic and is about the behavior of Table.ReplaceValue. Below a table in PWQ

income	kindhome	teenhome
5011	0	0
4600	1	1
7000	0	0
2600	1	1
5800	1	0
6300	0	1
5567	0	1
3333	1	1

I d'ont understant the two different results between

= Table.ReplaceValue(  #"Typage", each [kidhome], each 2, 
  Replacer.ReplaceValue,
  {"kidhome", "teenhome"})

and

= Table.ReplaceValue(  #"Typage",   each [kidhome],   each 2, 
  (curval,oldval, newval)=> newval,
  {"kidhome", "teenhome"})

Subsidiary question

Table.ReplaceValue(
   table as table,
   oldValue as any,
   newValue as any,
   replacer as function,
   columnsToSearch as list,
) as table

Why a personal replacer function is declared why 3 parameters mandatory (x,y,z) => ?

How each parameter is well associated with the 3 first parameters of Table.ReplaceValue ?

Thanks by advance for your help ?

[v-mdharahman]

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.

[ZhangKun]

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"}
)

[AntrikshSharma]

@ZhangKun Greate examples! For the last example I believe you wanted to write:

(x, y, z) => if y = 102 then z else x

For those who might find x, y, z difficult to understand at first.

Table.ReplaceValue (
    #table ( { "A", "B" }, { { 1, 2 }, { 11, 11 }, { 101, 102 } } ),
    each [B],
    666,
    ( CurrentValue, OldValue, NewValue ) =>
        if OldValue = 102 
        then NewValue 
        else CurrentValue,
    { "A" }
)

[ZhangKun]

This is an example from a little back ago, and I was trying to say that the second and third parameters of Table.ReplaceValue do not necessarily have to be used in the fourth parameter.

[dufoq3]

Hi @informer, check Rick's article.

[AlienSx]

@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...

[informer]

Thanks for your help

The 4th parameter is a function

I don't understant the 2nd exemple because it's a personal function and Replacer.ReplaceValue is never called

(curval,oldval, newval)=> newval,

In the example below, Replacer.ReplaceValue is not called

= Table.ReplaceValue(
 Source, 
 each [Acronyme], 
 null, 
 (x,y,z) => 
    List.Accumulate(
      List.Reverse(Text.PositionOf(x, y, Occurrence.All, Comparer.OrdinalIgnoreCase)),
      x, 
      (s,c)=>Text.RemoveRange(s, c, Text.Length(y))),
 {"Nom", "Volume", "Circulating Supply"})

In the Table.ReplaceValue, nothing is precised about parameters to pass in

replacer as function,

 I'm sure I'm missing something about construction of a personal function !!!

[AlienSx]

what's so special about Replacer.ReplaceValue? It's one of many other functions in M. You may use any other custom function with the same list of agruments. All you need to now is that Table.ReplaceValue passes exactly 3 values into it's 4th argument (the function we are talking about). By "passes" I mean that Table.ReplaceValue calls that function like we call any other function in M. Back to your examples: 

1. The one with Replacer.ReplaceValue: full syntax of this "replacer" is 

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

and basically it compares value with old and if value = old then it replaces value with new. Your "old" argument always has a value of kidhome field. And when Table.ReplaceValue works with "kidhome" column then value is always equal to old. That's why kidhome column = 2 (as you defined new) after transformation. 

When working with teenhome field, it's value will be replaced by 2 only when [teenhome] = [kidhome] (or when value = old). That's why replacements are made in rows 1 through 4 and 8. 

2. The one with custom function: here your custom function always returns a value of new with is 2 in your case - without regard of any other values in any other column. That's why both column's values got replaced with 2.

Table.ReplaceValue is not easy to understand and I feel your frustration. Good luck!

[informer]

Thanks AlienSx for your help.

Does (x,y,z) =>mean that  the Replacer function is called and applies the code after () => so in a way a new dynamics function is declared and called  (=> Repalcer.Newfunction) ?

If it's so it's the one great step for ma undestanding of
(x,y,z) =>

Is it the M langage philosphy  to assign implictly to the function parameters, the objects declare above the function ?

[AlienSx]

@informer I am not sure I follow you here. (x, y, z) => ... is explicit function declaration with 3 mandatory agruments of type any. You may assign an identifier to this function like this

my_function = (x, y, z) => ...function body...

and then use it in your code. Maybe in Table.ReplaceValues

= Table.ReplaceValue(
   #"Typage",
   each [kidhome],
   each 2,
   my_function,
   {"kidhome", "teenhome"}
)

[informer]

I still don't understand how x refers implicitly to the the first TABLE.REPLACEVALUE paramètre 

y the second  TABLE.REPLACEVALUE parameter

z the third TABLE.REPLACEVALUE parameter

(x,y,z) are never assigned before passing into the custom function

Normaly there is an assignation like

x = #"Source",

y = each [Acronyme],

z = null

And why I need to pass [Acronyme] whereas I need Acronyme of the current row 

See my post Power Query -Delete column substring corresponding to the text of another column (insensitive case)  ?

[lbendlin]

Normaly there is an assignation like

x = #"Source",

y = each [Acronyme],

z = null

No, there is no rule like that - this is different for each of the functions. You can use whatever acronym you want, in whatever order you want .

Please read the primer.

[informer]

Ben Gribaudo talks about order here and I publish an excerpt below

This implies that you can write M code in any order you’d like, even if that order is out of order—though  in order coding will probably be easier for other humans who read your code. Below, these two expressions are both valid and equivalent in their output even though their child expressions are given in opposite order. In both cases, M figures out the correct execution order to use when it processes them.

let
Data = { 1, 2, 3},
Result = List.Transform(Data, each _ * 10)
in
Result

let
Result = List.Transform(Data, each _ * 10),
Data = { 1, 2, 3}
in
Result

[slorin]

Bonjour @informer 

Je vais écrire en français, je ne maîtrise pas suffisament l'anglais

Nous avons échangé sur ces sujets sur le forum developpez.net

Les arguments 2 et 3 de Table.ReplaceValue sont basés sur la ligne = l'enregistrement en cours.
each [Acronyme] est équivalent à each _[Acronyme], c'est à dire le champ "Acronyme" de l'enregistrement en cours (avec le each _)

Notez que Ben Gribaudo évoque l'ordre du code dans un let...in, pas à l'intérieur d'une fonction.

Comme d'autres l'ont déjà expliqué,  la fonction en 4eme argument de Table.ReplaceValue prend elle même comme argument les 3 premiers arguments Table.ReplaceValue dans l'ordre (x,y,z) mais vous pouvez les appeler comme vous voulez

Autre exemple, suposons que vous vouliez calculer la factorielle de votre liste data 

let
Data = { 1, 2, 3},
Result = List.Transform(Data, each Number.Factorial(_))
in
Result

mais vous pourriez tout à fait écrire : 

let
Data = { 1, 2, 3},
Result = List.Transform(Data, Number.Factorial)
in
Result

vous obtenez dans les deux cas, {1, 2, 6}

la fonction Number.Factorial a pris comme argument implicite chacun des éléments de la liste

le _ de la première solution désigne chacun des éléments un par un

Dans Table.ReplaceValue vous ne manipulez pas une liste mais une table. le _ désigne alors un record, c'est à dire chacune des lignes une par une qui constituent la table

Vous me disiez le 10/02 que vos connaissances sur le langage M sont toutes nouvelles, vous touchez là des sujets complexes de ce langage, il m'a fallu beaucoup de temps pour apprendre et comprendre les subtilités (et je ne connais encore pas tout, loin de là).

Stéphane

[informer]

Bonjour Stéphane et merci une nouvelle fois pour répondre à mes question. Je sévis sur ce site pour ne pas vous surcharger sur le sujet !!!

Vos explications sont très didactiques et claires mais beacoup de concepts me déroutent. Par exemple :

• Je ne sais pas comment les affectations se font pour les 3 variables (x,y,z) car jamais il n'y a  x = #"Source", y = each [Acronyme], z = null ?
• Pourquoi est-il obligatoire de passer 3 paramètres alors que je redéfinis une fonction personnalisée donc avec sa propore définition sachant que z n'est jamais utilisé ?
  J'ai essayé avec 2 paramètres , il n'a pas d'erreur mais il n'y a pas de modification !
• Comment se fait la concordance entre la ligne courante et [Acronyme] passé en paramètre ?

----------------------------------------------------------------------------------------------------

Many concepts confuse me. Like what:

• I don't know how the assignments are made for the 3 variables (x,y,z) because there is never x = #"Source", y = each [Acronym], z = null?
• Why is it mandatory to pass 3 parameters when I redefine a custom function with its own definition knowing that z is never used?
  I tried with 2 settings, it has no error but there is no modification!
• How is the concordance between the current line and [Acronym] passed as a parameter?

[slorin · Accepted solution]

@informer 

Sur les points 1 et 3

quand vous ajoutez une colonne à une table, vous ne vous demandez pas ce que veut dire le [xxxx]

exemple, si vous créez une nouvelle colonne avec les 2 derniers caractères d'une autre colonne 

= Table.AddColumn(Source, "Column2", each Text.End([Column1], 2), type text)

le [Column1] désigne le texte contenu dans le champ intitulé "Column1" de votre ligne en cours

cela vous semble naturel !

vous pourriez également écrire : 

= Table.AddColumn(Source, "Column2", (_) => Text.End(_[Column1], 2), type text)

ici j'ai mis un _ mais vous auriez pu mettre un x ou informer

cette syntaxe est valable

= Table.AddColumn(Source, "Column2", (informer) => Text.End(informer[Column1], 2), type text)

 il est implicite que "informer" est l'enregistrement en cours (de l'étape "source")

La fonction Table.ReplaceValue est plus complexe car la fonction du 4e argument utilise les 3 arguments précédents pour elle-même. quelque soit les noms qu'on leur donne (x,y,z) ou (a,b,c) ou (informer1,informer2,informer3)

= Table.ReplaceValue(Source, Argument2, Argument3, (informer1,informer2,informer3)=>informer2, {"Column1"}

 ici on remplacera la column1 par le valeur du 2e argument

ce peut être du texte, un nombre, une liste... lié à la ligne en cours si on a un each...

mais également une valeur d'une autre requête

un exemple tiré par les cheveux pour bien comprendre, 

let
Source = Table.FromColumns({{1..3}},{"Column1"}),
Replace = Table.ReplaceValue(Source, Source[Column1], (toto)=> Source , (a,b,c)=>a, {"Column1"})
in
Replace

ne changera rien à la column1, on prend la valeur du premier argument (donc la column1) et on le remet.

let
Source = Table.FromColumns({{1..3}},{"Column1"}),
Replace = Table.ReplaceValue(Source, Source[Column1], (toto)=> Source , (a,b,c)=>b, {"Column1"})
in
Replace

on n'a pas de each, on n'est donc pas dans la ligne en cours, on prendre la colonne "Column1" de la table "Source" c'est à dire la colonne elle-même (en entier !) 

on aura donc une liste {1..3} dans chaque cellule.  

enfin, 

let
Source = Table.FromColumns({{1..3}},{"Column1"}),
Replace = Table.ReplaceValue(Source, Source[Column1], (toto)=> Source , (a,b,c)=>c, {"Column1"})
in
Replace

ici la fonction renvoie c, le 3eme argument que j'ai volontairement écrit (toto)=> (toto ou _ ou each, c'est la même chose, c'est la ligne en cours

sauf que là, je ne renvoie pas un champ spécifique de la ligne avec des [  ] mais Source, donc l'étape précédente.

Dans toutes les cellules j'aurais donc la table Source avec sa seule colonne de trois valeurs, 1, 2, 3.

Quant au point 2, Power Query ne génère pas d'erreur lorsqu'il n'y a pas assez d'argument, mais les modifications ne sont pas effectuées
Si par exemple j'écris : 

= Table.ReplaceValue(Source, each null, each null , Date.Day, {"Column1"})

Date.Day est une fonction avec un seul argument, je n'ai aucune erreur, mais la fonction ne remplace rien.

= Table.ReplaceValue(Source, each null, each null , List.Accumulate, {"Column1"})

pas d'erreur non plus avec List.Alternate qui demande pourtant 3 arguments. la fonction renvoie un erreur, il ne se passe rien. peut-être un bug de Power Query ?

Est-ce plus clair ?

Stéphane

[informer]

Stéphane,
Le voile de l'incompréhension se lève doucement grâce à vous et à toute la communauté toutefois je trouve que la documentation sur M qui est disponible sur MSN et internet en général est parcellaire, lacunaire et peu explicite !

Par exemple pour Table.ReplaceValue, la documentation se résume à une définition

Table.ReplaceValue(
   table as table,
   oldValue as any,
   newValue as any,
   replacer as function,
   columnsToSearch as list,
) as table

Et comment deduire de

replacer function

Qu'une fonction personnalisée doit obligatoirement passer 3 paramètres alors que si on appelle les 2 fonctions ci-dessous, aucun paramètre n'est demandé ?

Replacer.ReplaceValue et Replacer.ReplaceText

Exemple

= Table.ReplaceValue(#table( {"text", "Col1", "Col2"}, { {"azeurty",1, 2}, {"azeurty",3, 4} } ),
"ur", "", Replacer.ReplaceText, {"text"})

 output
text Col1 Col2
azety 1 2
azety 3 4

Et comment déduire que si une fonction personnelle est implémentée, en plus de devoir passer 3 paramètres ( x,y,z) => ,

x = Source

y = oldValue

z = NewValue

Sauf s'il existe une documentation qui l'explique mais je n'ai jamais reussi à accéder à cette information. Ou alors j'ai vraiment raté plus qu'une marche !!!

[ronrsnfld]

It might help to look closely at Example 4 in the MS documentation for Table.ReplaceValue

And what might be confusing is that, although Table.ReplaceValue passes 3 values to the fourth argument, they are not passed "in order".

Rather, if we use (x,y,z)

x	refers to fifth argument	the list of columns to act on
y	refers to the second argument	nominally the old text
z	refers to the third argument	nominally the replacement text

[informer]

Hi  ronrsnfld,

Thanks for yoour help

For each step forward, I take at least 2 steps back
Take a look at the page Learn microsoft - powerquery M - Table.Replacevalue he definition of the function is

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

I modifyed the 4th example as below

= Table.ReplaceValue(
    Table.FromRecords({
        [Name = "Cindy", Country = "US", test = "zozo"],
        [Name = "Bob", Country = "CA", test = "zaza"]
    }),
  each [test] = "zaza",
   null,
    (row, test, z) =>
         if test then "?" else row ,
    {"Name", "Country"}
)

And my cmonclusions are :

• Talk about OldValue  is for me not relevant because in my example OldValueits a condition on a 3rd column . Same for or NewValue because , I can assign the condition also to a or NewValue
• Nowhere it's explained that if a custom function REPLACER is declared, why 3 paramters are mandatory and what is assigned to them ?
• The fecth on each row is not clearly explained

[ronrsnfld]

The fact that three parameters are passed to the 4th argument is implied by Example 4, and also by the Replacer function which has three non-optional parameters.

"What is assigned to them" can also be seen in the fourth example, where it is clear that the first parameter corresponds to the 5th argument, the second to the second, and the third to the third argument of the Table.ReplaceValue function. (Sometimes it is not so clear).

It is not laid out in more detail because it is part of the architecture of how the M language works, and you'd have to delve deeply into the architecture and specifications to better understand that. Power Query M formula language