Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ErisedAlurem
Frequent Visitor

Using a custom function in Text.ReplaceValue

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:

ErisedAlurem_0-1658415745367.png

 

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()

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

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

vkkfmsft_0-1658818212463.png

 

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.

View solution in original post

2 REPLIES 2
v-kkf-msft
Community Support
Community Support

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

vkkfmsft_0-1658818212463.png

 

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
Super User
Super User

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.