# Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

## Power Query Functions That Return Functions

You’re probably aware that, in Power Query, a query can return a function. So for example here’s a very simple query (so simple that no let statement is needed) called MultiplyTwoNumbers with the following definition:

(x as number, y as number) => x * y

It can be used on the following table in Excel:

…to multiply the numbers in the column called Number by two and show the result in a custom column like so:

let

Source = Excel.CurrentWorkbook(){[Name=”Data”]}[Content],

#”Inserted Custom” = Table.AddColumn(Source, “Custom”, each MultiplyTwoNumbers(2, [Number]))

in

#”Inserted Custom”

Here’s the output:

It’s also the case that a function can return another function. Consider the following query, called MultiplyV2:

let

EnterX = (x as number) =>

let

EnterY = (y as number) => x * y

in

EnterY

in

EnterX

It is a function that takes a single parameter, x, and it returns a function that takes a single parameter, y. The function that is returned multiplies the value of x by the value of y. Here’s an example of how it can be used on the table shown above:

let

//Return a function that multiplies by 2

MultiplyBy2 = MultiplyV2(2),

Source = Excel.CurrentWorkbook(){[Name=”Data”]}[Content],

//Use the MultiplyBy2 function in a custom column

#”Inserted Custom” = Table.AddColumn(Source, “Custom”, each MultiplyBy2([Number]))

in

#”Inserted Custom”

This gives exactly the same result as before:

In this query, the MultiplyBy2 step calls the MultiplyV2 function with the argument 2, and this returns a function that multiplies the values passed to it by 2. This function can then be called in the final step where the custom column is added to the table using the expression MultiplyBy2([Number])

Interesting, isn’t it? I hope this satisfies your curiosity Marco J

Written by Chris Webb

August 24, 2014 at 10:09 pm

Posted in Uncategorized

### 10 Responses

1. Yes, very good example – thanks!

Marco Russo

August 25, 2014 at 6:18 am

2. […] Power Query Functions That Return Functions (Chris Webb) […]

3. This is a sorely overlooked feature of the M language which is a missed opportunity for Mister-softee IMHO. At first this seems like a neat trick, but using this process enables a lot of functionality and ease of development. This allows users to break big complex behaviors down into manageable snippet functions. I would love to hear more on the topic.

Derik

September 10, 2014 at 5:42 pm

4. Thanks for this inspiring post.It has helped me find the solution to the following case: I needed to import data from Excel reports in a folder. These reports come in 4 different formats. I therefore created 4 functions to import each format. With the technique described in the post, I have created a wrapper function that returns the import function corresponding to the format parameter.

Bertrand

October 29, 2014 at 11:03 am

5. Hi Chris,

I have this problem.
A b c d e
Row1 1 2 3 3 1

Has only 1 = 300 rows
has 1 and 2 = 200 rows
has 1 and 3 = 100 rows
has 2 and 3 no1 = 50 rows

I used to add column and do countif in normal excel work well but not scalable. Still pulling my hair off to get this done through Power Query.

champkris

December 12, 2014 at 12:38 pm

6. Hi Chris
A b c d e f
Row1 1 2 3 3 3 1

I need to out put this table

The program need to read through column a-f of each row and summarize:
Has only 1 = 500 rows
has 1 and 2 and no3 = 300 rows
has 1 and 3 and no 2 = 200 rows
has 2 and 3 and no1 = 100 rows

I used to do countif for each condition via normal excel. But for power query. Still pulling my hair off.

champkris

December 12, 2014 at 12:43 pm

7. Chris, could you look by the link below please and see is there any possible way to optimise that logic by function?

http://www.mrexcel.com/forum/power-bi/823305-power-query-allowed-values.html

Basically I need to check 12 columns so is there any way to implement a dynamic column name in function which would substitute tables with allowed values for each?…

It looks like a very tough task for me…

Mer

January 22, 2015 at 1:31 pm

• I’ll reply on the forum…

Chris Webb

January 22, 2015 at 2:49 pm

• Thank you so much Chris!!! It was brilliantly perfect!

Mer

January 23, 2015 at 7:42 am