Custom Function with Columns

[MBreden]

Hi all,

I want to create a simple function from a step of a query, initially without parameters.

The step of the query uses an if then statement:

= Table.AddColumn(GeänderterTyp, "Typ-A", each if [Typ] ="A" then [Article] else null)

The following function returns an error:

let 
    MyFunction =() as text =>     
     if [Typ] ="A" then [Article] else null
in 
     MyFunction

What's my mistake? How should I write the function?

Thanks Melanie

[Jimmy801 · Accepted solution]

Hello @MBreden,

you have to pass to your function all parameters you need. In this case it would be the both information for typ and article.

Your function should look like

customfunction =(type, article) => if type = "A" then article else null

you can then invoke your function llike this

Table.AddColumn(GeänderterTyp, "Typ-A", each customfunction([Typ],[Article] )

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[MBreden]

Hi, Jimmy,

thank you, that's how it works.
Since the columns Typ and Article are always the same, I didn't think I had to pass them as parameters.
My function now looks like this:

CallMyFunc = Table.AddColumn(ChangedType, "Typ A", each fxGetHName([Typ], [Article], "A")

// fxGetHName
let 
    MyFunction =(Typ, Article, txtTyp as text) =>     
     if Typ =txtTyp then Article else null      
in 
     MyFunction

But now comes the next problem.
I want to take another step through the function.
The newly created column is to be filled out downward:

Table.FillDown(CallMyFunc,{"Typ A"})

How do I bring this command into my function?

Greetings
Melanie

[Jimmy801]

Hello @MBreden 

question... why do you even need custom function for this straightforward steps? Also of the addColumn-step a function is not really ncessary.

all the best

Jimmy

[MBreden]

Hi, Jimmy,

in total I need 5 new columns with flags from A to E, so I thought of a custom function.
I come from Excel/VBA and am still pretty new to Power Query and M-Code.
This task from a current project is good for me to learn functions better.

Greetings
Melanie

[Jimmy801]

Hello @MBreden 

all right.. then a good learning 🙂

however for your second question you need to craete a function that has a table and maybe also the column name as parameter

Good luck

Jimmy

[MBreden]

Thank you Jimmy,

I have now added the "FillDown" function directly in the query using a dynamic ColumnNames list with the new columns.

Greetings

Melanie