My Practice Tips When Using The Power BI Advanced Editor

We’re going to talk about some of my best practice tips using Advanced Editor inside Power BI. You may watch the full video of this tutorial at the bottom of this blog.

Surprisingly, most people don’t know about the Advanced Editor. It is because it’s hidden away inside the Query Editor, which a lot of users don’t utilize as much as they should.

If you don’t know what it is, you need to have a better understanding of how it operates in Power BI.

The Advanced Editor records all of the different transformations you make inside the Query Editor.

You don’t need to grasp how to write any of the code (called ‘M’). But you must have a good understanding of what it actually means and how it works behind the scenes.

You have to know this is because there are so many times especially when you make more and more transformations inside the Query Editor that you’ll need to complete some fixes within the Advanced Editor.

There are certainly adjustments that can be more easily made within the Advanced Editor and via M code versus doing it within the UI just by pointing and clicking.

I wanted to do a quick dive into the advanced editor inside of the query editor in Power BI. If you are just starting out in Power BI, you might not be familiar with this area but as you develop more and more Power BI reports, it becomes important to have an understanding of what is going on inside here.

Table of Contents

Query Editor

The first thing to do is to go to the query editor by selecting the icon Edit Queries. 0:50

In all my beginner trainings, I always say to never click on Get Data because data is never perfect; you always have to do some sort of transformation or cleanup.

As you start doing more work in Power BI, you will need to make some small adjustments in what’s called the Advanced Editor.

This is where lines of code get written when you make transformations on your data sets. I’m currently on the Dates table looking at the source where the data is coming from, which comes from Dates Query.

The key thing to note here is that this table was created from a source, and the source in this case is just a parameter query. It has been labeled with one line of code called Renamed Columns.

Let’s go and make some changes and create a Short Month column. I will duplicate the column, and then split the column by number of characters.

I’m just going to divide it by three, and choose Once, as far left as possible.

I will then rename the column to Short Month. You will be able to see the steps I applied on the right side.

The key here is inside the advanced editor, which will show you what actually happened. What’s great about it is that it tries to figure out a label for every single row inside of here, and sequentially writes code for every single step that you do.

In my opinion, you do not need to know how to write code to use Power BI well. I don’t even know how to write it myself because the advanced editor does all the work for you. Obviously, I’m missing some very niche things that I can’t do because I can’t write it out, but the important thing here is to understand what is going on.

Making Changes In The Editor

The great thing is you can make changes that will be reflected in the table. For instance you don’t want to call it “Short Month” anymore, and you want to change it to “Short Mth.”

You can come inside the editor and make the changes here, and it makes the change for you on the front end. This is pretty handy from an auditing perspective, simply because when you get errors, this is the place to go. You can dive into here and figure out where the error occurred.

Fixing The Error

I want to show you one quick thing where you can get into a bit of trouble and it can only be fixed in the advanced editor.

Say for instance the Short Mth column disappears and then it appears at the end of the table. You might want to move it and place it next to the full month, right?

Now if we jump to the advanced editor, you’ll see that the Reordered Columns actually list every single column name.

You have to be aware of this happening. Let’s say you realized at this point that you wanted to change the name of something, or even put something as simple as a space between Week and Ending, it will give you a warning that says Insert Step.

At the moment it looks fine and nothing’s wrong when you click, but then check out what happens when you go to the end. You get an error.

Sometimes, these error codes don’t always tell you exactly what’s wrong or where to find the error. But in this case, it is saying the WeekEnding can’t be found. Now the reason for this is because the initial table name was actually listed in this particular row.

So you have to go and find the row and that particular column name. You can see here that “WeekEnding” hasn’t changed, causing the error. So all we have to do is put a space between Week and Ending to fix the error.

This is why having an understanding of the advanced editor is key. Hopefully you can become much more familiar with the advanced editor, because you might have a lot of errors to fix and you don’t want to delete all of them.

Conclusion

My main point is that having an understanding is more important than being able to write any code. That’s my main takeaway for you from this tutorial.

If you want to learn many advanced query editor techniques, check out the content contained within this one course module at Enterprise DNA Online. This is the course where most members see the biggest transformation from their work in Power BI once they have completed it.

Advanced Data Transformations & Modeling

Sam