Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
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
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
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
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