Custom Power Query Function to replace a Text in a column base on conditions

[Mic1979]

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

[shafiz_p]

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!!

[Greg_Deckler]

@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

[Mic1979]

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

[Mic1979 · Accepted 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.