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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Mic1979
Post Partisan
Post Partisan

Custom 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

1 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.

View solution in original post

4 REPLIES 4
shafiz_p
Super User
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!!

Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

Top Solution Authors