RADACAD Blog

Power Query Function that Returns Multiple Values

Posted by on Jul 30, 2015 in Power BI, Power Query | 5 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 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 eight 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: http://www.radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

5 Comments

  • 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

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