Using a custom function in Text.ReplaceValue

[ErisedAlurem]

I made a post earlier (deleted it because I meneged to solve it myself) about a custom function that I was trying to create to use in Text.ReplaceValue (in the replacer as function argument). From what I've seen, people normaly use Replacer.ReplaceValue or Replacer.ReplaceText; or use a function in the format of (x,y,z) => where x = Columns you want to replace the value; y = old value; z = new value. The function I created has four arguments (x,y,z,n) where n always starts as 0. The function is as follows: functionTest:

let
TesteFunction = (x as text, y as list, z as list, n as number) =>
let 
w = List.Count(y) - 1,
z = if y{n} = z{n} then z else List.InsertRange(z,n,{""}),
x = Text.Combine(
    if y{n} = z{n} then z else List.InsertRange(z,n,{""})
,";"),
Passo2 = if n = w then x else @TesteFunction(x,y,z,n + 1)
in 
    Passo2
in  
TesteFunction

It works as expected when applying it outside the  Text.ReplaceValue function. The idea is:

'y' is a list of strings such as {"1","2","3","4","5"}, 'z' is a list in the format {"1","3","5"} and 'x' is the 'z' list in text format, separeted by ";", therefore "1;3;5" and 'n' been the iterator; the wanted output is "1;;3;;5" (making the string the same lenght as the y list, and matching by the value of each record in the list).

Example of calling the function:

When I try to call the function insde the Text.ReplaceValue() it doesn't understand what x,y,z,n is and the query doesn't work. Here is the code:

Tentativapt1 = Table.ReplaceValue(#"Changed Type",
    each Text.Split([Base], ";"),
    each Text.Split([Retorno], ";"),
    functionTest(x,y,z,0)
    ,
    {"Retorno"})

 How do I make power query understand what x, y and z means? I considered declaring the function inside my query, instead of calling the function itself, as seen below:

Tentativapt1 = Table.ReplaceValue(#"Changed Type",
    each Text.Split([Base], ";"),
    each Text.Split([Retorno], ";"),
    let
TesteFunction = (x as text, y as list, z as list, n as number) =>
let 
w = List.Count(y) - 1,
z = if y{n} = z{n} then z else List.InsertRange(z,n,{""}),
x = Text.Combine(
    if y{n} = z{n} then z else List.InsertRange(z,n,{""})
,";"),
Passo2 = if n = w then x else @TesteFunction(x,y,z,n + 1)
in 
    Passo2
in  
TesteFunction(x,y,z,0)
    ,
    {"Retorno"})

It results on the same error "Expression.Error: The import x matches no exports. Did you miss a module reference?".

Here's the source code (using the (x,y,z) => function, not my costum - if you want apply my function, simply replace

(x,y,z) =>
if y{0} = z{0} then z else List.InsertRange(z,0,{""})

this part) if anyone wants something more concrete:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ2sja2NrE2VdIBso2BdKwOkqi1GVDcCCyPJA4UA5GxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Base = _t, Retorno = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Base", type text}, {"Retorno", type text}}),
    Tentativapt1 = Table.ReplaceValue(#"Changed Type",
    each Text.Split([Base], ";"),
    each Text.Split([Retorno], ";"),
    (x,y,z) =>
    if y{0} = z{0} then z else List.InsertRange(z,0,{""})
    , {"Retorno"}
    ),
    Tentativapt2 = Table.ReplaceValue(Tentativapt1,
    each [Retorno],
    null,
    (x,y,z) =>
    Text.Combine(y, ";")
    ,
    {"Retorno"})
in
    Tentativapt2

  Any help would be appreciated

Summing up: Made a costum function, don't know how to apply it to Table.ReplaceValue()

[v-kkf-msft · Accepted solution]

Hi @ErisedAlurem ,

Please try the code.

= Table.ReplaceValue(#"Changed Type",
    each Text.Split([Base], ";"),
    each Text.Split([Retorno], ";"),
    (x,y,z) => functionTest(x,y,z,0),
    {"Retorno"})

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Couple of comments

- You can keep using Replacer.ReplaceValue and put your logic into the third parameter of Table.ReplaceValue

- if you want to replace Replacer.ReplaceValue with a function be aware that it expects three parameters, not four. The meaning of the parameters is slightly different from what you describe

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

Replacer.ReplaceValue - PowerQuery M | Microsoft Docs