Forum Discussion
Post PartisanCustom Power Query Function to replace a Text in a column base on conditions
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
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.
4 Replies
- Greg_Deckler
Community Champion
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
Mic1979Thanks 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_TextHowever 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
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.
- shafiz_p
Super User
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!!
