Power Query Function that Returns Multiple Values

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:

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:

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:

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:

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:

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:

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:

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

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.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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.

5 thoughts on “Power Query Function that Returns Multiple Values

  • Is it possible to dynamically return a sub set of a date field dynamically within power bi to be used as an axis label? i.e. instead of first and last day, a period based on a selection e.g. 3 month, 6 month, 9 month etc?

    Thanks for all of your posts!!

    • Hi Daniel
      Yes, you can create that banding. You do need to create a list in Power Query of the number of months to add, then add it to your main table, and expand it with using Date.AddMonths function to create periods of 3 months each.
      You can also do this in Power BI using Grouping (I mean outside of Power Query)
      Cheers
      Reza

  • Thanks Reza
    It’s perfect!!
    But I’ve a question. For example. If I’ve a table with cost values, but for example, this month (September), I don’t have the value and I need to bring the last value (August). What can I do?

    • Hi Igor
      If you have a table with months and values, and you do not have a value in a month, but you want to fill it with the value of previous or even next month, you can use FILL UP or FILL DOWN transformation in Power Query.

Leave a Reply