Here’s something interesting that I just discovered about how Power Query deals with function parameters of type list…
Imagine you have the following table in an Excel worksheet, and a Power Query query called MyTable that loads all of the data from it:
Now, create the following function in a new query:
[sourcecode language=”text” padlinenumbers=”true”]
(Mylist as list) => List.Sum(Mylist)
[/sourcecode]
This is just declares a function that takes a single parameter, Mylist, that is a list and it returns the sum of all of the values in that list.
Now invoke the function from the Workbook Queries pane and instead of seeing the normal Enter Parameters dialog box you’ll see the following:
Clicking on the Choose Column button displays this:
Here you can select another query from your workbook and then select a single column from that query. In this case I’ve chosen column A from the MyTable query. Click OK and all of the values from that column will be passed as a list (using the expression MyTable[A]) through to the new function. Here’s what the resulting query to invoke the function looks like:
[sourcecode language=”text”]
let
Source = Test(MyTable[A])
in
Source
[/sourcecode]
The output in this case is, of course, the value 6 – the sum of all of the values in column A:
I’ll use this in a more practical scenario in a future blog post!
Dear Chris is there a way to perform a sum orizzontally like anchoring the first column? In a sum Excel function would be : Sum($A1:b1). The idea is to work out YTD figures when the months are on columns rather then rows.