What is Advanced Editor in Power BI?

If you've spent any time cleaning data in Power BI, you've been using the Power Query Editor. While clicking through the interface to split columns and filter rows feels straightforward, there’s a powerful engine working tirelessly behind the scenes. This article pulls back the curtain on that engine, known as the Advanced Editor, showing you how to take direct control of your data transformations.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is the Advanced Editor, Anyway?

The Advanced Editor is a window within the Power Query Editor that exposes the underlying code for all your data preparation steps. Every time you click a button in the Power Query interface - like "Remove Columns," "Change Type," or "Filter Rows" - Power BI writes a line of code for you. The Advanced Editor is simply where all of that code lives.

This code is written in a language called Power Query Formula Language, but everyone just calls it "M." You don't need to be a programmer to use the Advanced Editor, but understanding what it does unlocks a new level of control, efficiency, and clarity over your data.

Think of it like this: the Power Query interface is like driving an automatic car. It's easy, and it gets you where you need to go. The Advanced Editor is like looking under the hood and learning to drive stick - it gives you more precise control and a deeper understanding of how the machine actually works.

Why You Should Care About the Advanced Editor

Ignoring the Advanced Editor is fine for simple projects, but you're leaving a lot of power on the table. Here are a few key reasons why getting comfortable with it is a game-changer:

• Full Transparency: The "Applied Steps" pane on the right gives you a list of your actions, but the M code in the Advanced Editor shows you the exact logic. This is fantastic for troubleshooting when a transformation isn’t behaving as you'd expect. You can see precisely what’s happening at every stage.
• Unlocking Complex Logic: While the user interface is great for common tasks, some advanced transformations are easier or only possible by writing or tweaking the M code directly. This includes creating custom functions, adding detailed comments, or implementing complex conditional logic.
• Boost Your Efficiency: Need to perform the same set of 15 steps on three different tables? Instead of clicking through the menus 45 times, you can copy the entire M code block from one query and paste it into another. You can also reorder and modify steps much faster than pointing and clicking.
• Share and Reuse Your Work: M code is completely portable. You can copy the code for a tricky data-cleaning query and share it with a teammate or save it in a text file to reuse in a future Power BI report. It’s the ultimate way to standardize your data prep process.

A Step-by-Step Guide to Using the Advanced Editor

Let's get hands-on and walk through how the Advanced Editor works with a simple, practical example.

1. Opening the Advanced Editor

First things first, you need to open the Power Query Editor. In Power BI Desktop, you can get there by clicking the Transform data button on the Home ribbon.

Once you're in the Power Query Editor, you can find the Advanced Editor in two places:

• Under the Home tab, click the Advanced Editor button.
• Under the View tab, you'll also see an Advanced Editor button.

Clicking this opens a new window showing the M code for your selected query.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Understanding the Structure of M Code

At first glance, the code might look intimidating, but its structure is surprisingly simple. Nearly every M query follows the same let / in structure.

• let: This is where every data transformation step is defined. Each step is given a variable name, and it usually builds upon the variable from the previous step.
• in: This final line simply states which of the variables from the let block should be the final output of the query. 99% of the time, this will be the name of the very last step you defined.

Let's imagine you've performed a few basic steps on a simple table of sales data:

1. Connected to a CSV source file.
2. Promoted the first row to be headers.
3. Changed the OrderDate column to a Date type.
4. Removed the CustomerID column.

If you look at the "Applied Steps" pane, you’ll see each of those actions listed.

When you open the Advanced Editor, you’ll see the M code that Power BI generated for you:

<pre><code>let // 1. Connects to the source CSV file Source = Csv.Document(File.Contents("C:\Data\SalesData.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]), // 2. Uses the first row of data as the column headers #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), // 3. Changes the data type of the OrderDate and Amount columns #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"OrderDate", type date}, {"Amount", type number}}), // 4. Removes the CustomerID column from the table #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"CustomerID"}) in #"Removed Columns"</code></pre>

Notice a few things:

• Each step is a variable (e.g., Source, #"Promoted Headers", #"Changed Type"). Step names with spaces need to be enclosed in #"".
• Each line ends with a comma, except for the very last step inside the let block (#"Removed Columns").
• Each step (except the first) uses the variable name of the previous step as its starting point. It's a chain of transformations.
• The in section tells Power BI to output the final result, which is the table after we removed the columns.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

3. Making Edits in the Advanced Editor

Now, let's make a few simple edits directly in the code to see how easy it is.

Renaming a Step for Clarity

The step #"Changed Type" is a bit generic. Let’s rename it to something more descriptive like #"SetDataTypes". To do this, you need to change the variable in two places:

• Where it is defined.
• Where it's referenced in the next step.

Here’s the updated code after our change:

<pre><code>let Source = Csv.Document(File.Contents("C:\Data\SalesData.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), // Change the variable name here #"SetDataTypes" = Table.TransformColumnTypes(#"Promoted Headers",{{"OrderDate", type date}, {"Amount", type number}}), // And update the reference to it here #"Removed Columns" = Table.RemoveColumns(#"SetDataTypes",{"CustomerID"}) in #"Removed Columns"</code></pre>

Click "Done," and you’ll see the step renamed in your "Applied Steps" pane. This minor edit makes your query much easier for others (or your future self) to understand.

Adding Comments

You can add comments to your code to document why you made a certain change. This is a best practice that the standard Power Query interface doesn't offer. You can add a single-line comment using // or a multi-line block comment with /* ... */.

<pre><code>/* This query cleans the raw sales data. Last updated by Alex on October 26th. */ let Source = Csv.Document(File.Contents("C:\Data\SalesData.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"SetDataTypes" = Table.TransformColumnTypes(#"Promoted Headers",{{"OrderDate", type date}, {"Amount", type number}}), // Removing this column because it's not needed for the sales report #"Removed Columns" = Table.RemoveColumns(#"SetDataTypes",{"CustomerID"}) in #"Removed Columns"</code></pre>

Practical Examples to Get You Started

Here are a couple of common scenarios where the Advanced Editor is much more powerful than the user interface.

Example 1: Dynamic Date Filtering

Imagine you have a report that should always show data for the current year. Using the interface, you might filter the date column for '2024'. But when January 1st, 2025 rolls around, your report will be broken until you manually update it.

With the Advanced Editor, you can use an M function to make it dynamic.

Add a new step to your code right before the in statement. This step will filter your OrderDate column based on the current year.

<pre><code> ... // Your previous steps #"Removed Columns" = Table.RemoveColumns(#"SetDataTypes",{"CustomerID"}), // New step to filter for the current year automatically #"Filtered to Current Year" = Table.SelectRows(#"Removed Columns", each Date.Year([OrderDate]) = Date.Year(DateTime.LocalNow())) in #"Filtered to Current Year" </code></pre>

Don't forget to update the variable in the in section to your new final step! Now, your report will automatically filter to the current year, whether it's 2024, 2025, or beyond - no manual updates required.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example 2: Creating a Reusable Function

Let's say you frequently need to clean up text fields by trimming whitespace and converting them to proper case (e.g., " john smith " becomes "John Smith"). You could perform these two steps manually every time, or you could create a simple custom function with the Advanced Editor.

Create a new Blank Query (right-click in the Queries pane > New Query > Blank Query). Then, open the Advanced Editor and paste this code:

<pre><code>(inputText as text) => let TrimmedText = Text.Trim(inputText), ProperText = Text.Proper(TrimmedText) in ProperText </code></pre>

Name this query fnCleanText. Now, back in your main data query, you can use this function to clean a column. Select the column you want to clean (e.g., "CustomerName"), go to the "Add Column" tab, and choose "Invoke Custom Function."

Select your fnCleanText function, and Power BI will add a new column with the cleaned-up names. This is a simple example, but it illustrates how you can package repeatable logic to save an enormous amount of time.

Final Thoughts

The Power BI Advanced Editor isn't something to be afraid of. It's a transparent look at the data preparation steps you're already doing, offering a pathway toward more complex, efficient, and repeatable transformations. By understanding the simple let and in structure, you can start making small tweaks that save you time and add valuable context to your data models.

At the end of the day, all these tools are about getting from raw data to clear insights as smoothly as possible. The challenge with powerful tools like Power BI is often the significant learning curve required for things like M code. At Graphed we remove this complexity entirely. We designed an AI data analyst that allows you to connect your marketing and sales platforms - like Google Analytics, Shopify, and Salesforce - and build real-time dashboards just by describing what you want to see in plain English. No more searching for buttons in a complex interface or learning a new coding language, just ask for the report you need, and Graphed builds it for you.