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:

image

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:

image

Clicking on the Choose Column button displays this:

image

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:

image

I’ll use this in a more practical scenario in a future blog post!

4 thoughts on “Power Query And Function Parameters Of Type List

  1. 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.

Leave a Reply to Benford’s Law And Power Query | Chris Webb's BI Blog Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s