Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Power Query And Function Parameters Of Type List

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:

(Mylist as list) => List.Sum(Mylist)

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:

let
Source = Test(MyTable[A])
in
Source

The output in this case is, of course, the value 6 – the sum of all of the values in column A:

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.

Follow Blog via Email

Social

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk