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:

let
    FirstAndLastDayOfTheMonth = (date) => //function definition
         let
            dated=Date.FromText(date),//convert input text to Date 
            year=Date.Year(dated),//fetch year
            month=Date.Month(dated),//fetch month
            FirstDateText=Text.From(year)&"-"&Text.From(month)&"-01",//generate text value of the first date
            FirstDate=Date.FromText(FirstDateText)//convert text value to date
         in
            FirstDate//return result of the function
in
    FirstAndLastDayOfTheMonth("30/07/2015")//function call

and the Result:

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:

let
    FirstAndLastDayOfTheMonth = (date) => 
         let
            dated=Date.FromText(date),
            year=Date.Year(dated),
            month=Date.Month(dated),
            FirstDateText=Text.From(year)&"-"&Text.From(month)&"-01",
            FirstDate=Date.FromText(FirstDateText),
            daysInMonth=Date.DaysInMonth(dated),//fetch number of days in month
            LastDateText=Text.From(year)&"-"&Text.From(month)&"-"&Text.From(daysInMonth),
            LastDate=Date.FromText(LastDateText)
         in
            LastDate
in
    FirstAndLastDayOfTheMonth("30/07/2015")

and the result:

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:

let
    FirstAndLastDayOfTheMonth = (date) => 
         let
            dated=Date.FromText(date),
            year=Date.Year(dated),
            month=Date.Month(dated),
            FirstDateText=Text.From(year)&"-"&Text.From(month)&"-01",
            FirstDate=Date.FromText(FirstDateText),
            daysInMonth=Date.DaysInMonth(dated),
            LastDateText=Text.From(year)&"-"&Text.From(month)&"-"&Text.From(daysInMonth),
            LastDate=Date.FromText(LastDateText),
            record=Record.AddField([],"First Date of Month",FirstDate),
            resultset=Record.AddField(record,"Last Date of Month",LastDate)
         in
            resultset
in
    FirstAndLastDayOfTheMonth("30/07/2015")

and the result:

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:

FirstAndLastDayOfTheMonth("30/07/2015")[Last Date of Month]

and the result would be a single value

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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.