Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Creating And Using Functions in Data Explorer (Power Query)

with 14 comments

NOTE: This post was written before Data Explorer was renamed as Power Query. All of the content is still relevant to Power Query.

I’ve been reading through the Data Explorer Formula Language and Library specifications (which can be downloaded here) over the last few weeks, learning about what’s possible with it when you go beyond the UI. This will translate into a lot of Data Explorer blog posts in the coming weeks…! Here’s the first of those posts.

Today I’m going to look at how to create and use functions. Page 86 onwards in the Formula Language specification has a lot of detail about how to define a function in the M language that Data Explorer uses, but it doesn’t include any practical examples of how functions can be created and used in Data Explorer and Excel unfortunately. I’ve done some experimentation, though, and this post aims to fill that gap.

First of all, take a look at this Excel worksheet with two tables in it, called InputNumbers1 and InputNumbers2:

image

Let’s say that you want to create two Data Explorer queries that each use one of these tables as a source, and in both cases you want to add a third column that displays the product of the two existing columns. So, for example, for the first table you want a new column with the values 2, 12, 30 and 56 in.

Now this is a very simple calculation and certainly one that Data Explorer can handle easily, but let’s say that you want to perform this operation many times in many different queries and for obvious reasons you don’t want to have to duplicate the same logic in each query. You want to create a function!

To do this, you need to use the Write Query option from the From Other Sources button on the Data Explorer tab in the ribbon:

image

Then change the name of the new query to MultiplicationFunction by double-clicking on the query name in large letters at the top of the Edit Query dialog:

image

Then enter the following code as the only step:

= (x,y) => x * y

This defines a query with two parameters, x and y, and returns the product of x and y. Click Done to save the query. The query will show the following result in the worksheet:

image

You can ignore this new table, and you can disable the data load onto the worksheet, but it seems like you always need to have the table there and you certainly can’t delete it.

To use this new function create a new query from the table InputNumbers1 (shown above) using the From Table button. Then in the Edit Query dialog create a new step by selecting Insert Column/Custom:

image

And then use the new function to calculate each row in the new column, passing the two existing columns as parameters:

image

This results in the following step:

= Table.AddColumn(Source, “Custom”, each MultiplicationFunction([FirstNumber],[SecondNumber]))

image

And so you have your new column showing the product of the [FirstNumber] and [SecondNumber] columns. Then do the same thing for the InputNumbers2 table and you’ll have two Data Explorer queries now with custom columns in:

image

image

Finally, to make sure that the function is indeed being used, go and alter the definition of the function to be

= (x,y) => x / y

Refresh both of the other queries, and you’ll see the contents of the custom column have changed for both of them:

image

image

I can imagine that the ability to break functionality out into functions will reduce the complexity, and improvement the maintainability, of many ETL solutions that get built with Data Explorer in the future.

You can download my sample Excel workbook with this demo in here.

Written by Chris Webb

March 25, 2013 at 5:22 pm

14 Responses

Subscribe to comments with RSS.

  1. Hi Chris!

    Just a quick question on data explorer completely unrelated to this post: Is it limited by Excels row limit?

    Peter K

    March 29, 2013 at 6:42 am

    • It will run into that limit if you decide to output to the worksheet, but before that no, it isn’t limited by Excel

      Chris Webb

      March 29, 2013 at 10:30 am

  2. [...] defines a new function that takes a single parameter, x, and returns the value x+1. See this post for more details on using function in Data [...]

  3. [...] UnPivot() function; with all the data in a single column it was then relatively easy to declare the functions GetValue() and GetNeighbours() to apply the rules of Life, output the results in a new column in [...]

  4. […] the heart of my approach was a recursive function. I’ve blogged about creating functions in Data Explorer already, so you might want to read that post for some background. Here’s my function […]

  5. […] to the end users. I also wonder whether it will be possible to create a series of relatively simple Power Query functions, share them and then use them as building blocks to create more complex queries. This is something […]

  6. […] to the end users. I also wonder whether it will be possible to create a series of relatively simple Power Query functions, share them and then use them as building blocks to create more complex queries. This is something […]

  7. Hi Chris,

    can you please tell me, how can i see my views in my source database (Oracle 9)?

    When i connect my workbook with the source database i see all the tables but the views are not there!

    What is to do?

    Thank you very much in advance.

    Refa

    September 24, 2013 at 10:17 am

  8. […] a query is all very well, but in Power Query it’s also possible to share functions (which are, after all, just a different type of query) too – which is the subject of this post. […]

  9. […] a query is all very well, but in Power Query it’s also possible to share functions (which are, after all, just a different type of query) too – which is the subject of this post. […]

  10. […] now let’s calculate a year-to-date running sum. In this case, I decided to use a function (see here for some background on this, and here for a more complex example of a function) to do the […]

  11. […] now let’s calculate a year-to-date running sum. In this case, I decided to use a function (see here for some background on this, and here for a more complex example of a function) to do the […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,310 other followers

%d bloggers like this: