Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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()
Solved! Go to 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.
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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
87 | |
67 | |
49 |
User | Count |
---|---|
135 | |
113 | |
100 | |
68 | |
67 |