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),
//Load data from the table
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
You can download the sample workbook for this post here.
Yes, very good example – thanks!
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.
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.
Hi Chris,
I have this problem.
A b c d e
Row1 1 2 3 3 1
Read and output as:
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.
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.
This should be possible. You need to do something like the following:
1) Create a custom column containing a list value, where the list contains all the values in the current row
2) Use List.Contains() on these lists to find out whether they contain the given values
The following post shows something vaguely similar to what you want to do:
https://cwebbbi.wordpress.com/2014/01/27/comparing-columns-in-power-query/
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…
I’ll reply on the forum…
Thank you so much Chris!!! It was brilliantly perfect!
Great how to… thank you.
Can you invoke a function from another query to create a column in that query… if so how is the syntax different?
Sorry for the rookie question.
Yes, you can do that. The syntax is no different.
Thomas:
The trick with writing good functions to use for adding columns is that you often want to examine multiple fields in each row (e.g. if column A is true then column B * Columns C else Column B * [Value from some External Source]).
This sort of problem requires more tinkering when creating an invoke-able function – namely, your function should take a record as input. This way, you can call the function and pass the entire row (which is a record) to the calling function. The the step to call the function to add a row will look like this:
myStep = Table.AddColumn(prevStep, “myColumnName”, each myFunction(_))
*caveat ‘each’ may not be required
The external function might take on a form like this:
myFunction // saved as separate query/function
(myRecord as record) =>
let
checkColA = myRecord[Column A] = “something”, // test that gives true or false,
results = if CheckColA
then myRecord[Column B] * myRecord[Column C]
else myRecord[ColumnB] * List.Sum(myExternalSource)
in results
This technique works really well for separating the function logic out so that it can be worked on as it’s own separate function. The one issue that I have run into is that as your function starts calling external data sources and when you are calling it for tables with lots of rows, the overall query will get very ‘chatty’ (read: slow).
The best way around this is to find points to buffer in external sources I THE CALLING QUERY, that way these stay in memory ad don’t need to be repeatedly accessed for each row. Not sure how technically accurate my description is – but there is definitely room for optimization if you find these taking longer than you think they should.
Hi – I’m trying to produce a list of lists to pass to a TransformColumns() function. I just don’t know how to return the function “each 0” to the the list. I can return the type number (I think), but the “each 0” needs to be the second item in the list.
Here is the code I have so far:
let
SplitDay = 5,
Source = List.Combine( {List.Transform({1..SplitDay}, each “rday__” & Number.ToText(_)),List.Transform({1..SplitDay}, each “oday__” & Number.ToText(_)),List.Transform({1..SplitDay}, each “dday__” & Number.ToText(_))}),
Custom2 = List.Zip({Source,List.Transform({1..List.Count(Source)}, each “each 0” ),List.Transform({1..List.Count(Source)}, each type number )})
in
Custom2
Well, what do you know! Apparently each each 0 works. Though I swear I tried that before and PQ complained about it.
let
SplitDay = 5,
Source = List.Combine( {List.Transform({1..SplitDay}, each “rday__” & Number.ToText(_)),List.Transform({1..SplitDay}, each “oday__” & Number.ToText(_)),List.Transform({1..SplitDay}, each “dday__” & Number.ToText(_))}),
Custom2 = List.Zip({Source,List.Transform({1..List.Count(Source)}, each each 0 ),List.Transform({1..List.Count(Source)}, each type number )})
in
Custom2