Print on screen parameters passed to a custom function

[Mic1979]

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.

[lbendlin]

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.

[Mic1979]

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

[lbendlin]

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.

[Mic1979]

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:

• {[OutputColumn1]}
• {"OutputColumn1"}

but in both cases it is not working.

What is the problem here?

Many thanks again

[lbendlin]

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"}
)

[Mic1979]

Thanks but I always get the same error:

[lbendlin]

show the full code, ideally in a sample file.

[Mic1979]

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

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

[Mic1979]

It works.

Many thanks. Now I need to study it.

[lbendlin]

Chris Webb's BI Blog: Expression.Evaluate() In Power Query/M (crossjoin.co.uk)

but especially: 

Power Query M Primer (Part 22): Identifier Scope II – Controlling the Global Environment, Closures |...