Power Query Function that Returns Multiple Values

Posted by on Jul 30, 2015 in Power BI, Power Query | No Comments
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

5

Yesterday in NZ BI user group meeting, I’ve been asked that does Power Query custom functions return only one value as the result set? or they can return multiple values. I’ve answered Yes, and I’ve explained that through a sentence how to do it with Records, List, or Table. Then I thought this might be a question of many people out there. So I’ve wrote this blog post to illustrate how to return multiple values from a custom function in Power Query.

If you don’t know how to create a custom function, please read my other blog post with an example of Day Number of Year function for Power Query. In this post I’ll show you through an example of how to return multiple results from a Power Query function.

As you probably know Power Query function return single value by default, and that is the value result of the operation in the “in” clause of the function. Now how to return multiple values? Simply by returning different type of object. The trick is that Power Query custom function can return any single object. and that object can be simple structure object such as Date, Text, Number. Or it can be multiple value objects such as Record, List, and Table.

To understand difference between Record, List, and Table:

  • Record : Is a single record structure with one or more fields. each field have a field name, and a field value.
  • List: Is a single column structure with one or more rows. each row contains a value.
  • Table: Multiple rows and columns data structure (as you probably all know it ;) )

Above objects can hold multiple values. So the only thing you need to do in return one of above objects based on your requirement. In example below I’ve returned a Record as a result set, but you can do it with other two data types.

Return First and Last Dates of Month

As an example I would like to write a function that fetches both first and last date of a month, the input parameter of this function is a date value with text data types, such as “30/07/2015″.

** Note that Date Conversion function is locale dependent. So if the date time of your system is no DD/MM/YYYY then you have to enter date as it formatted in your system (look below the clock on right hand side bottom of your monitor to check the format).

Let’s start by calculation of First Date of the Month

First Date of the Month

We need to fetch the year, and the month, and then built a date string for first day (day 1) of that month and year, and finally convert it to Date datatype.

Here is the script:

and the Result:

1

Last Date of the Month

For fetching last date of the month, we use same method of first date, except one change. which is the day part of the calculation should be number of days in the month, which comes from Date.DaysInMonth function.

Here is the script:

and the result:

2

Combining both values into a Record and returning Record as a Result

Now we have both values and we want to return them both. I’ll create an empty record first. Empty record can be created simply with this : [] .

Then I used Record.AddField function to add fields one by one. Record.AddField gets three parameters: the record that field will be added to it, name of the new field, and value of the new field.

Here is the script:

and the result:

3

How to access this Record’s values

As you see in above the function returns a record with two fields. now we can access fields by name, with script like this:

and the result would be a single value

4

 

Here is the full code of example if you want to try it yourself.

Don’t Limit Yourself

You can have another record in a field’s value, you can have a list in a field’s value, and you can have a table in a field’s value. so you can create any data structure that you want as the result set of your function.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">