Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Dear all,
I do hope someone can help me also in this case.
I have the following custom function:
(
Input_Table as table,
InputColumn1 as text, // Product_Series_Description
InputColumn2 as text, // Port_Type_1_2
InputColumn3 as text, // Connection_Type_Description*/
InputColumnToChange1 as text // 3rd_Port
) =>
let
NewTable1 = Table.ReplaceValue (
Input_Table,
each [InputColumnToChange1],
each if [InputColumn1] = "2 way Angle Body Valve"
then "NOT APPLICABLE"
else [InputColumnToChange1],
Replacer.ReplaceText,
{InputColumnToChange1}
),
NewTable2 = Table.ReplaceValue (
NewTable1,
each InputColumnToChange1,
each if InputColumn1 = "3 way Angle Body Valve" and
InputColumnToChange1 = "NOT APPLICABLE" and
InputColumn3 = "G/Rp (ISO 228-1 + ISO 7-1)"
then "G+Rp"
else if InputColumn1 = "3 way Angle Body Valve" and
InputColumnToChange1 = "NOT APPLICABLE" and
InputColumn3 = "ISO 7/1 Rc"
then "Rc"
else if InputColumn1 = "3 way Angle Body Valve" and
InputColumnToChange1 = "NOT APPLICABLE" and
InputColumn3 = "NPTF (ANSI 1.20.3)"
then "NPT"
else InputColumnToChange1,
Replacer.ReplaceText,
{InputColumnToChange1}
)
in NewTable2
invoked in this way:
let
Source = RULE1(Summary_Volumes, "Product_Series_Description", "Port_Type_1_2", "Connection_Type_Description", "3rd_Port")
in
Source
However the table is not the one I wanted to have. I suspect the parameters are not passed correctly to the function when invoked.
How could I plot on screen just the value passed to the custom function?
I had a background with another language, and to check the different steps, I used to print on screen the different steps.
Thanks a lot.
Solved! Go to Solution.
ah, now I get it. You are trying to abstract a column value replacement with arbitrary tables and arbitrary columns.
This is a bit more complex, and requires Expression.Evaluate
(Input_Table as table, InputColumn1 as text, OutputColumn1 as text) =>
Expression.Evaluate(
"Table.ReplaceValue (
Input_Table,
each ["
& OutputColumn1
& "],
each if ["
& InputColumn1
& "] = ""2 way Angle Body Valve""
then ""NOT APPLICABLE""
else ["
& OutputColumn1
& "],
Replacer.ReplaceValue,
{"""
& OutputColumn1
& """})",
[
Table.ReplaceValue = Table.ReplaceValue,
Replacer.ReplaceValue = Replacer.ReplaceValue,
Input_Table = Input_Table
]
)
Welcome to the world of von Neumann, lex and yacc. There be dragons.
The values are whatever you invoke the function with. Shouldn't you know what they are?
For troubleshooting you may want to start with smaller versions of the function, one parameter at a time.
I followed your suggestion and I made it very simple:
(
Input_Table as table,
InputColumn1 as text // Product_Series_Description
// InputColumn2 as text, // Port_Type_1_2
// InputColumn3 as text, // Connection_Type_Description*/
// InputColumnToChange1 as text // 3rd_Port
) =>
let
Input_List = Input_Table[InputColumn1]
in Input_List
Invoked in this this way:
let
Source = #"RULE1 (2)"(Summary_Volumes, "2 way Angle Body Valve")
in
Source
with this error:
I really do not understand what I am making not correctly.
Could you please support?
Thanks a lot in advance
let
Input_List = Input_Table[InputColumn1]
in Input_List
This will not work.
Try
let
Input_List = SELECTCOLUMNS(Input_Table,{InputColumn1})
in Input_List
Note that this returns a single column table, not a list.
Many thanks for your big help.
Now it seems fine, I reviewed my function integrating it a little bit:
(
Input_Table as table,
InputColumn1 as text,
OutputColumn1 as text ) =>
let
#"InputColumn1" = Table.SelectColumns(Input_Table,InputColumn1),
#"OutputColumn1" = Table.SelectColumns(Input_Table,OutputColumn1),
NewTable1 = Table.ReplaceValue (
Input_Table,
each [#"OutputColumn1"],
each if [#"InputColumn1"] = "2 way Angle Body Valve"
then "NOT APPLICABLE"
else [#"OutputColumn1"],
Replacer.ReplaceText,
{OutputColumn1}
)
in NewTable1
However I have an error:
I think the error is here:
{OutputColumn1}
I tried:
but in both cases it is not working.
What is the problem here?
Many thanks again
You cannot use Replacer.ReplaceText in that scenario. Must use Replacer.ReplaceValue
NewTable1 = Table.ReplaceValue (
Input_Table,
each [OutputColumn1],
each if [InputColumn1] = "2 way Angle Body Valve"
then "NOT APPLICABLE"
else [OutputColumn1],
Replacer.ReplaceValue,
{"OutputColumn1"}
)
Thanks but I always get the same error:
show the full code, ideally in a sample file.
Here the code:
(
Input_Table as table,
InputColumn1 as text,
OutputColumn1 as text ) =>
let
#"InputColumn1" = Table.SelectColumns(Input_Table,InputColumn1),
#"OutputColumn1" = Table.SelectColumns(Input_Table,OutputColumn1),
NewTable1 = Table.ReplaceValue (
Input_Table,
each [#"OutputColumn1"],
each if [#"InputColumn1"] = "2 way Angle Body Valve"
then "NOT APPLICABLE"
else [#"OutputColumn1"],
Replacer.ReplaceValue,
{"OutputColumn1"}
in NewTable1
Here how I invoked the function:
let
Source = RULE1(Summary_Volumes, "Product_Series_Description", "3rd_Port")
in
Source
ah, now I get it. You are trying to abstract a column value replacement with arbitrary tables and arbitrary columns.
This is a bit more complex, and requires Expression.Evaluate
(Input_Table as table, InputColumn1 as text, OutputColumn1 as text) =>
Expression.Evaluate(
"Table.ReplaceValue (
Input_Table,
each ["
& OutputColumn1
& "],
each if ["
& InputColumn1
& "] = ""2 way Angle Body Valve""
then ""NOT APPLICABLE""
else ["
& OutputColumn1
& "],
Replacer.ReplaceValue,
{"""
& OutputColumn1
& """})",
[
Table.ReplaceValue = Table.ReplaceValue,
Replacer.ReplaceValue = Replacer.ReplaceValue,
Input_Table = Input_Table
]
)
Welcome to the world of von Neumann, lex and yacc. There be dragons.
It works.
Many thanks. Now I need to study it.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
13 | |
9 | |
7 | |
6 |