Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Dear all,
I am approaching in these days to the customized function in Power Query, I am really new and not familiar with this topic.
My target is to reaplace a value in a column table based on conditions in other columns.
Obiviously, as I am new to this, I wanted to start first with a simple case.
So, I created a function simply reaplacing a text with another if a certain condition occurs. This is the function:
let
Change_Text=(Text) =>
Text.Replace (
if Text="a" then "f"
else Text)
in Change_Text
However, I got the following error message:
An error occurred in the ‘Change Text’ query. Expression.Error: 1 arguments were passed to a function which expects 3.
Details:
Pattern=
Arguments=[List]
Could you support me?
Thanks in advance
Solved! Go to Solution.
Finally I found the solution:
let
Change_Text_Column=(Input_Table as table) =>
Table.ReplaceValue (
Input_Table,
each [#"Text"],
each if [#"Text"]="3" then "12"
else [#"Text"],
Replacer.ReplaceText,
{"Text"})
in Change_Text_Column
This is the results obtained invoking the function:
Start Text
3 12
4 4
5 5
7 7
9 9
The point for me now is to use it adding a column in a table adding a column.
It seems not possible to use as input parameter the same table where I want to add the column by invoking the function. I have an error message.
Thanks for your help.
Hi @Mic1979 Use this code to create input parameter.
let
Change_Text_Column = (TableName,ColumnName,Old_V,New_V) => Table.TransformColumns(
TableName,
{{ColumnName, each Text.Replace(_, Old_V, New_V), type text}}
)
in
Change_Text_Column
Call this function into your table or wherever you want to use it. Remember that the Text.Replace function operates on text values, so your column should be of type text. Additionally, make sure to enclose the column name within double quotation marks, as well as the Old_V and New_V values.
If I answered your question, please mark it as a solution!!
@Mic1979 Text.Replace takes 3 arguments:
Text.Replace(text as nullable text, old as text, new as text) as nullable text
So your function should be something like:
let
Change_Text=(Text) =>
Text.Replace (
[SomeColumn], "SomeText", if Text="a" then "f"
else Text)
in Change_Text
Thanks a lot.
No I don't get any error message. I made this:
let
Change_Text=(Text) =>
Text.Replace (
Text,
"Text",
if Text="a" then "f"
else Text)
in Change_Text
However it seems that the condition is not working, and whatever text I insert, I have the same text as output.
Any suggestions?
Thanks in advance
Finally I found the solution:
let
Change_Text_Column=(Input_Table as table) =>
Table.ReplaceValue (
Input_Table,
each [#"Text"],
each if [#"Text"]="3" then "12"
else [#"Text"],
Replacer.ReplaceText,
{"Text"})
in Change_Text_Column
This is the results obtained invoking the function:
Start Text
3 12
4 4
5 5
7 7
9 9
The point for me now is to use it adding a column in a table adding a column.
It seems not possible to use as input parameter the same table where I want to add the column by invoking the function. I have an error message.
Thanks for your help.