Power BI Custom Query Functions

For today’s tutorial, we’re going to build a Power BI custom Query function that’s going to deal with a common difficult-to-handle format of stacked data. This technique is easily reusable on multiple reports and multiple tables. You can watch the full video of this tutorial at the bottom of this blog.

This is a continuation of the tutorial I did recently where we dealt with a common difficult-to-handle format of stacked data and used Modulo, Pivot, and some cleaning functions with Power Query to transform that data into an organized and functional format.

It’s very likely that you’re going to need to repeat this process. If this type of format resulted from a copy and paste, it’s probably going to happen again. So, in this case, you’ll probably want to build a custom function that’s going to be reusable on multiple reports and multiple tables, regardless of how they’re named and what else is in those tables.

Table of Contents

How To Create And Debug Power BI Custom Query Functions

Let’s jump into Power Query on this. If we go to our Data table, in the Advanced Editor, we’ve got the M code for how we ended up going from that stack data to the desired format.

If we want to reuse this, we just right-click on Data and say, Create Function.

It says it’s not going to reference any parameter, but it’s okay to create a function without parameters. We’re going to answer, Yes, because we’re going to add the parameters as we go in the Advanced Editor. We’ll call this function, Unstack – 3 Elements, just because we’ve got the three rows in the raw format. If we had a two-row format, we’d probably build a separate one that was two elements or four.

So now, we’ve got this Invoke, we’ve got no parameters, and let’s go to the Advanced Editor and start working on this one.

First, we don’t need this Source. We’re going to use the function parameters to define our source.

We want to define the parameter that’s going to be coming in. We do that with an open parenthesis and then a parameter name. Let’s call this, Stack, which is going to be that one column of data that is the unformatted stack data that came in from the paste of the email addresses.

We want this to come in as a list and we want this to result in a table because as we unpivot it from that single column to multiple columns, it goes from a list to a table. And now, we need to convert that incoming list to a table so that we can add our index, initiate our Modulo unpivot, and do all the things we need to do to reformat it.

We’ll call it ConvertStack, and that is going to use a function called Table.FromList. Like many of the M functions, it does exactly what it says. It just takes a list. In this case, the list is going to be our Stack list. And then, we need to replace the Source here with our ConvertStack. It looks pretty good! We’ve got no syntax errors, so let’s hit Done.

Now it’s giving us what we wanted, which is the ability to choose a column.

Then, we’re going to choose TestData, which is the misformatted data. We’ll choose the Value column, and then click OK. After that, we click Invoke.

However, we get this error. It’s really hard to pinpoint what this error is. One of the problems with custom functions is that they don’t break out the Applied Steps you just get one step for the entire custom function. But there’s a way around that in the debugging that I’ll show you. It’ll make it a lot easier to figure out what’s going on here.

Instead of running this for the moment as a function, we’re going to run it as a query. We’re just going to manually initiate that Stack call. We’ve got to define what Stack is because we’re not drawing it through the interaction of choosing it in the Invoke. So, we have Stack equal to TestData, and the Value column of TestData.

What it does is it breaks it down now into the Applied Steps. This will help us figure out why this isn’t working. Notice one interesting thing when we pull Stack in. Initially, it pulls in TestData[Value], but when we convert it to a table, instead of Value is the column header, the column header is now Column1.

Remember the error message, it was that it couldn’t find the Value column. The reason it couldn’t find the Value column is that, the Table.FromList function renames that to Column1.

If we go down the Applied Steps, you can see that it is in the Added Custom step where we get the error. This is where the Value of the record wasn’t found.

And so, if we go to the Advanced Editor, we find that Custom field, and we can see that it is in the Text.Remove where we’re taking out those junk characters that we didn’t need, but it’s still referring to the Value field. We’ll change that to Column1. Likewise, in the Removed Columns field, it refers to Value, so we’ll change that to Column1 as well.

And when we get to the end of the Applied Steps, it produces the perfect end result.

Now that we know this is working right, we just have one more thing to do. Remember that in order to debug it, we turned the custom function into a query. And so, now we need to take that query and turn it back into a function.

Conclusion

In this tutorial, we’ve built this custom function, debugged it, tested it, and it’s working great. And so now, anytime we have a three-element stack, we can just select that table, choose that column within the table, and run that custom function. Then, apply the Modulo, the unpivot, and clean up.

This should give you some nice tools in terms of creating custom functions. Also, remember that trick about debugging by turning it back into a query from a function and then switching it back after the debugging. It is a really valuable tool that makes it much easier to debug custom functions.

All the best!

Brian