Introduction to custom functions in Power Query (part 1)

In this video, we will introduce you to custom M functions in Power Query.

In the Power Query M formula language, a function is a mapping from a set of input values to a single output value.

This definition is described in more detail in the video by using an everyday example that is easy to follow and understand.

Check out our new Power Query fundamentals course!

#powerquery #powerbi #etltools #data #dataanalysis

[kumaraswamymandala]

Very good explanation to understand function in power query.

[chilaw]

The last thing I expected when I clicked on this video was a story about an ice cream man! But it’s a memorable image and helped me understand the logic of functions in M code. Thanks again!

[kebincui]

Vivid explation with easy understood simile😍

[damianocausale]

Very useful explanation. Using sample of ice cream man helps to understand the logical way to build a function in Power Query. Great job!

[chakrabmonoj]

Hey Sensei...I really like your videos a lot. They are extremely resourceful and far better than many others out there on YT. I am struggling with one of your videos earlier, in which you used a nested function which returns another function and used it within a query step to lookup values from another table.
Your code went something like this :
RateAnotherWay = Table.AddColumn(#"Added Custom1", "Rate_V1",
(TargetTable)=>
Table.SelectRows(RateMaster,
(SourceTable)=>
Text.Contains(TargetTable[Ingredient], SourceTable[Ingredient])))
This works within the query.
But when I tried to re-write the code as a stand alone function query as below :
(TargetTable as table)=>

let

RateAnotherWay =
Table.SelectRows(RateMaster,
each
Text.Contains(TargetTable[Ingredient], [Ingredient]))
in

RateAnotherWay

It returns an error : value as list Vs. value as text.

Is there a reason it is not working the way I wrote it? Can you help me please with the edit?

[malchicken]

Excellent general explanation, thank you. The video makes me think it would be interesting to see a video on ‘a custom function made to execute other custom functions’. It’s like getting to advanced layered programming. I took the “Sugar.Add” type examples as like custom functions, within this new custom function your creating which makes me think 🤔 it would be neat to make a ‘super custom function’ made of a bunch of other custom functions, which are made of power queries native functions. 👍