Custom Function Power Query (M) - Return Table

Question

I need a custom function that takes two parameters, Column1 and Column2, so:

For each Row, return the value of Column1 but only if exists a Value in the Column2 else return null

I have tried this:

let ColumnsFilter = (Tabla,C1,C2)=>

Table.AddColumn(Tabla, "Custom", each if [C2] <> null then [C1] else null)

in
    ColumnsFilter

And calling the function:

#"Previous Step" = .....
#"P" = ColumnsFilter(#"Previous Step","Column1","Column2")

in
    P

And is not working. clearly I am not using the syntax properly.

In summary I need a table as input and a table as output adding custom columns.

How can I write this?

(Please don't tell me to use the assisted of Power Query, I need to write similar functions manually)

Alexis Olson Alexis Olson · Accepted Answer · 2021-03-08T16:02:05

Since you're passing column names as text and individual rows are a record type, you have to use Record.Field to pull the right column (field) from the current row (record).

let
    ColumnsFilter = (Tabla as table, C1 as text, C2 as text) as table =>
        Table.AddColumn(Tabla, "Custom",
            each if Record.Field(_, C2) <> null then Record.Field(_, C1) else null
        )
in
    ColumnsFilter

Custom Function Power Query (M) - Return Table

1 Answers