What is M Query in Power BI?

Learning Power BI often starts with dragging and dropping fields to create visuals, but the real power lies in shaping your data before it ever hits a chart. This is where Power BI’s M language comes in, a powerful tool running just beneath the surface of the user-friendly Power Query Editor. This article breaks down what M is, why it's worth learning, and how you can start using it to take your data preparation skills to the next level.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is Power Query and Where Does M Fit In?

Before diving into M, it's crucial to understand Power Query. Power Query is Microsoft's data connection and preparation tool, integrated directly into applications like Power BI and Excel. It's the engine you use to connect to hundreds of different data sources, and then clean, transform, and reshape that data into a clean, usable format. Think of it as your data workshop.

When you use the Power Query Editor's graphical user interface (GUI) to perform common tasks, you're essentially clicking buttons to:

• Remove unwanted columns or rows.
• Filter data based on specific criteria.
• Change data types (e.g., text to number).
• Split a single column into multiple columns.
• Merge or append datasets together.

Each time you perform one of these actions, Power Query isn't just visually changing the data, it's writing code behind the scenes. That code is M. The full name is "Power Query Formula Language," but it's universally known as M.

You can think of the Power Query interface as a recorder. You press buttons to perform steps, and Power Query writes the M code "script" for you automatically. Every step you apply in the "Applied Steps" pane on the right-hand side corresponds directly to a line of M code.

Why Should You Bother Learning M?

If Power Query writes the code for you, why learn it at all? While the point-and-click interface is fantastic for beginners and handles about 80% of data transformation tasks, learning the M language directly unlocks the remaining 20% and offers several significant advantages.

1. Go Beyond the Graphical Interface

The Power Query GUI is powerful, but it has its limits. There are advanced data transformations that simply can't be accomplished with button clicks alone. Need to iterate through a list to perform a custom calculation or connect to nested data from a web API? You’ll need to edit the M code directly. Understanding M allows you to break free from the constraints of the interface and solve much more complex data challenges.

Graphed

Still Building Reports Manually?

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

Watch the Demo

2. Build Custom Functions for Reusability

Do you find yourself performing the same set of cleaning steps on multiple datasets every month? Instead of repeating those clicks over and over, you can write a custom function in M. For example, you could create a function that takes a messy text column, removes special characters, capitalizes the first letter, and trims whitespace - all in one go. You can then apply this single custom function to any query in any of your reports, saving a massive amount of time and ensuring consistency.

3. Greater Control and Easier Debugging

Sometimes, a query in Power BI breaks. The error messages can be cryptic, but they almost always point to a problem within the M script. If you can read the code, you can pinpoint exactly which step is causing the failure and why. It's like being able to read your car's diagnostic report instead of just seeing a "check engine" light. This control makes troubleshooting complex data issues infinitely easier and faster.

4. Improve Query Performance

Understanding what the M code is actually doing can help you write more efficient data transformation steps. Sometimes, the order in which you apply transformations can significantly impact how long your data takes to refresh. By editing the M code, you can optimize these steps - a technique known as "query folding" - to push more of the processing work back to the original data source (like a SQL database), leading to dramatically faster report refreshes.

Getting Your Hands Dirty with M

Now that you know the "why," let's look at the "how." The first step is learning where to find and how to read the M code your actions generate.

Finding the M Code

There are two primary places in the Power Query Editor where you can see M code:

1. The Formula Bar: Just like in Excel, Power Query has a formula bar. If you don't see it, go to the View tab and check the box for "Formula Bar." When you click on any of your "Applied Steps," the formula bar will display the single line of M code associated with that specific step. This is great for learning by seeing how your clicks translate into code.
2. The Advanced Editor: This is where the magic happens. On the Home or View tab, click "Advanced Editor." A new window will pop up showing the entire M script for your currently selected query, from connecting to the source to the final output. This is where you'll go to make significant changes or write code from scratch.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Basic Structure of an M Query: let and in

Every M script you'll see in the Advanced Editor follows a fundamental structure using two keywords: let and in.

• let: The let block is where all your data transformation steps are defined. Each step is assigned to a variable name. Crucially, each step typically uses the variable from the previous step as its input, creating a chain of sequential transformations.
• in: The in block is very simple. It follows the let block and specifies what the final output of the query should be. This is almost always the variable name of the very last step defined in the let block.

Here’s a simple, commented example of what a full script looks like:

let // Step 1: Connect to the source data (a CSV file) Source = Csv.Document(File.Contents("C:\Data\MySalesData.csv"),[Delimiter=","]),

// Step 2: Power BI automatically tries to promote the first row to headers
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteTopLevel=true]),

// Step 3: It also automatically changes some column types
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"SaleAmount", type number}}),

// Step 4: A custom step - Filter rows to only show sales from "North America"
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Region] = "North America"))

in // Final Output: The result of the #"Filtered Rows" step is loaded into Power BI #"Filtered Rows"

Notice how #"Promoted Headers" uses Source as its input, #"Changed Type" uses #"Promoted Headers", and #"Filtered Rows" uses #"Changed Type". It’s a step-by-step recipe.

A Practical Example: From UI to Full M Script

Let's walk through a simple scenario to see how UI actions build an M script. Imagine we have a CSV file with product sales data and we want to clean it up.

Our Goal: Load sales data, filter for a specific category, calculate profit, and remove unneeded columns.

Step 1: Get Data

We start by connecting to our CSV file using the Power Query interface. Power BI automatically generates the first few steps.

M Code Generated:

let Source = Csv.Document(File.Contents("C:...Sales.csv")), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteTopLevel=true]) in #"Promoted Headers"

Step 2: Filter by Product Category

In the "Category" column, we click the filter arrow and uncheck everything except "Electronics."

M Code Added: Power Query adds a new step called #"Filtered Rows".

...
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Category] = "Electronics"))
...

Step 3: Add a Custom Column for Profit

Next, we go to the Add Column tab, click "Custom Column," and create a new column named "Profit" with the formula [Revenue] - [Cost].

M Code Added: A new step is created to add the column.

...
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Profit", each [Revenue] - [Cost])
...

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Remove Unnecessary Columns

Finally, we don't need the original "Revenue" and "Cost" columns anymore. We select them, right-click, and choose "Remove Columns."

M Code Added: The final step removes the selected columns.

...
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Revenue", "Cost"})
...

The Final M Script

If we open the Advanced Editor now, we see the complete, sequential recipe our clicks have created:

let
    Source = Csv.Document(File.Contents("C:\...Sales.csv")),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteTopLevel=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Category] = "Electronics")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Profit", each [Revenue] - [Cost]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Revenue", "Cost"})
in
    #"Removed Columns"

By looking at this script, an experienced user can understand the entire data transformation process without ever seeing the UI. This is the clarity and power that learning M provides.

Final Thoughts

Power Query M is the powerful engine working behind the curtains of Power BI's intuitive data preparation interface. While you can accomplish a great deal without ever writing a line of code, learning even the basics opens up a new world of possibilities, allowing you to tackle more complex challenges, automate repeatable work, and get more precise control over your data transformations.

Of course, mastering a language like M takes time and patience. While it’s less intimidating than conventional programming, we realize many teams would rather focus on analyzing insights than learning another tool. At Graphed , we remove this friction entirely. Instead of clicking through steps or debugging M code, you can use natural language to connect your sources and create dashboards. You just describe what you want - "Show me total profit by product category for last month" - and get a live-updating report in seconds, freeing you up to act on your data instead of just preparing it.