What is M Code in Power BI?

If you’ve dipped your toes into Power BI for more than a few minutes, you’ve likely stumbled upon Power Query, the magical tool that lets you clean, shape, and transform your messy data into something useful. But behind every click, every filter, and every changed data type in the Power Query user interface, there’s a powerful scripting language working silently in the background. That language is called M, and this article is your guide to understanding what it is and why it's your secret weapon for next-level data prep.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is M Code, Anyway? A Simple Explanation

Think of the Power Query Editor as a professional kitchen. When you use the buttons and menus to transform your data - like splitting columns, removing rows, or changing text to lowercase - you’re like a chef using pre-set appliances. Click a button, and the oven turns on. Select a menu option, and the mixer starts a specific program. It’s easy, user-friendly, and perfect for most of your data-shaping needs.

M Code is the recipe that gets written down as you use those appliances. Every single action you take in the Power Query interface - every click, every selection - is automatically translated into a line of M code. So, while you're busy clicking around, Power BI is diligently writing the step-by-step instructions in its own language. The language’s formal name is “Power Query M Formula Language,” but everyone just calls it “M.” Its sole purpose is data transformation - getting your data cleaned up and ready for analysis and visualization.

Power Query vs. M Code: What's the Difference?

This is a common point of confusion, but the distinction is simple once you grasp the kitchen analogy:

• Power Query is the entire environment, the kitchen itself. It's the user interface where you can see your data, click buttons to apply transformations, and manage your data import process.
• M Code is the underlying scripting language that powers Power Query. It's the recipe that documents everything you do in the kitchen.

You can be a proficient Power BI user by only ever using the Power Query interface. But when you learn to read and write a little M code, you graduate from being a cook who can only use the pre-set buttons to a chef who can fine-tune the recipe, add custom ingredients, and create complex dishes that aren’t on the menu.

Why You Should Care About Learning M Code

Sticking with the user interface is completely fine for basic transformations, but eventually, you'll run into a problem that a button can't solve. Learning even a little M code opens up a new world of possibilities and control over your data preparation process.

Graphed

Still Building Reports Manually?

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

Watch the Demo

1. Go Beyond the User Interface

The Power Query editor is great, but it can’t possibly have a button for every conceivable data transformation. What if you need to perform a calculation based on the value in the previous row? Or split a column based on three different delimiters at once? Or create a conditional column with five nested "else if" conditions? These tasks range from clunky to impossible with the UI alone but are straightforward with a few lines of M code.

2. More Powerful and Customized Transformations

M Code gives you granular control. Instead of being limited to the default options, you can add optional parameters to functions to change their behavior. For example, the Table.SplitColumn function in the UI always splits to text, but with M code, you can tell it to split a text string directly into number columns, saving you a whole extra step.

3. Easier Debugging and Auditing

When you have a long list of applied steps in the Power Query pane, figuring out where something went wrong can be tedious. You have to click through each step to see how the data preview changes. By looking at the M code in the Advanced Editor, you can see the entire transformation process laid out in a single script. It's often much faster to spot a mistake in the code than to click through dozens of steps.

4. Create Reusable Functions

Perhaps the most powerful feature for time saving is creating custom M code functions. If you find yourself performing the same set of steps over and over again on different tables (like cleaning up phone numbers or standardizing country names), you can write that logic once as a custom function. After that, you can invoke that function on any table with a single click, saving an enormous amount of repetitive work.

Getting Started: Where to Find and View M Code

Before you can edit M code, you have to know where to find it. Thankfully, Power BI makes this very easy.

1. From the main Power BI Desktop window, click the Transform data button on the Home ribbon. This will open the Power Query Editor.
2. Select a query (a table) from the Queries pane on the left.
3. Look at the Applied Steps pane on the right. This lists every transformation you’ve applied in the UI.
4. Click on any of the steps. Now, look just above your data preview window, you’ll see the Formula Bar. This shows the M code for the selected step. If you can’t see it, go to the View tab in the ribbon and check the box for "Formula Bar."

This is great for seeing one line at a time. To see the entire script for a query, navigate to the Home tab in the Power Query ribbon and click on Advanced Editor. This will open a new window showing the full M code for all your applied steps.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Understanding the Basic Structure of an M Script

When you open the Advanced Editor, you’ll see some code that, at first glance, might seem intimidating. But nearly every M script follows the same simple structure using two main keywords: let and in.

Here’s the breakdown:

let Step1 = function_to_get_data(source), Step2 = function_to_transform(Step1), Step3 = another_function_to_transform(Step2) in Step3

• let expression: This is where all the work happens. The let block contains a series of steps, where each step is a variable assignment. Each step is separated by a comma. By convention, the step name often reflects what the UI would name it (e.g., #"Changed Type", #"Filtered Rows"). Each step typically uses the result of the previous step as its input.
• in expression: This determines the final output of the query. Whatever step name you put after in is what gets loaded into your Power BI model. It’s almost always the name of the final step from the let block.

Think of it like a chain reaction. Step1 loads the source data. Step2 takes the result of Step1 and modifies it. Step3 takes the result of Step2 and modifies it further. Finally, the in expression says, "Okay, once everything is done, show me the final result of Step3."

Practical Examples of M Code You Can Use Today

The best way to learn is by doing. Here are a few common scenarios where a small tweak in M code can save you a lot of time.

Example 1: Advanced Column Splitting

Let's say you have a column named "Product" with data like "SKU123 - T-Shirt" and you want to split it into two columns: "SKU" and "Product Name."

In the UI, you would use "Split Column by Delimiter," choose the hyphen, and it works perfectly. Power BI generates this M code:

Table.SplitColumn(Source, "Product", Splitter.SplitTextByDelimiter(" - "), {"Product.1", "Product.2"})

But what if some entries use a colon, like "SKU456: Hoodie"? The UI can't handle multiple delimiters in one pass. With M, you can provide a list of delimiters:

Table.SplitColumn(Source, "Product", Splitter.SplitTextByAnyDelimiter({" - ", ":"}), {"SKU", "ProductName"})

By simply replacing Splitter.SplitTextByDelimiter(" - ") with Splitter.SplitTextByAnyDelimiter({" - ", ":"}) and giving the columns better names, you just completed a task that would have taken more steps in the UI.

Example 2: A More Complex Conditional Column

Imagine you want to categorize sales deals into tiers based on their value in a "Revenue" column: "Small" (under $1000), "Medium" ($1000 to $5000), or "Large" (over $5000).

Using the UI's "Conditional Column" feature is easy for simple if-then logic. But when you have multiple conditions, it gets clunky. In M, the logic is much cleaner.

The UI helps you get started, but you can write a more elegant version directly in the Advanced Editor. You can add a new step using the fx button next to the formula bar and paste this in:

Table.AddColumn(PreviousStepName, "Deal Tier", each if [Revenue] > 5000 then "Large" else if [Revenue] >= 1000 then "Medium" else "Small" )

(Remember to replace PreviousStepName with the actual name of your last applied step!) This if...then...else structure is far more readable and scalable than clicking through menus to add more and more clauses.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Example 3: Adding a Conditional Index or Rank

This is an advanced task that's nearly impossible in the UI but incredibly useful. Let's say you have a table of sales transactions and you want to rank them within each sales region. You want to see the first, second, third, etc. sale for "North," then have the ranking reset for "South."

This requires grouping by region, adding an index to each sub-table, and then expanding.

let
    Source = YourDataSource,  // Your data source step
    #"Grouped Rows" = Table.Group(Source, {"Region"}, {{"AllRows", each Table.AddIndexColumn(_, "RegionRank", 1, 1), type table}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Date", "Sale Amount", "RegionRank"}, {"Date", "Sale Amount", "RegionRank"})
in
    #"Expanded AllRows"

This bit of code first segments your data by "Region." Then for each group, it adds an index column ("RegionRank") starting at 1. It then recombines everything so you get a ranking that resets per region.

Final Thoughts

M Code is the key to unlocking the full potential of Power BI's data transformation engine. While the user-friendly Power Query interface is perfect for most tasks, learning to read, tweak, and write M code allows you to solve complex problems, save time, and build more robust, efficient, and auditable data models.

Of course, the whole point of mastering M Code is to get cleaner data into your dashboards faster so you can find valuable insights. While this is powerful for data living in spreadsheets or databases, the real reporting struggle for many businesses is wrestling with live data from dozens of different marketing and sales platforms. This is where we built Graphed to help. We connect directly to tools like Google Analytics, Shopify, Facebook Ads, and Salesforce, handling the heavy lifting of data integration and prep so you don't even need to think about it. Instead of opening a script editor, you just ask a question in plain English, and Graphed instantly builds the real-time dashboard you need, powered by all your connected data.