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.
5 thoughts on “Power Query Function that Returns Multiple Values”
I just wanna say thanks for sharing!
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!!
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)
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?
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.