What is M Function in Power BI?

If you're using Power BI, you're interacting with the M language, even if you don't realize it. M is the powerful formula language engine working behind the scenes in the Power Query Editor, handling all your data preparation and transformation steps. This article explains what the M language is, how its syntax works, and shows you practical examples you can start using today.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly is M and Why Does it Matter?

M, officially known as the Power Query M formula language, is the language used for all data transformation and mashup activities inside Power BI's Power Query Editor. Think of it as the recipe for preparing your data. Every time you click a button in the Power Query interface - like "Remove Columns," "Split Column," "Filter Rows," or "Change Type" - Power BI is actually writing a line of M code in the background for you.

The primary purpose of M is to handle the "Extract, Transform, Load" (ETL) part of your business intelligence process. It's designed to:

• Connect to and import data from a huge variety of sources (Excel files, SQL databases, web pages, APIs, etc.).
• Clean and shape that raw data into a structured, usable format.
• Combine data from multiple sources.

It's crucial to understand M's place in the Power BI ecosystem, especially how it differs from DAX (Data Analysis Expressions). Here's the simplest way to think about it:

• M Language (Power Query): Used for data preparation. You use it to clean, filter, reshape, and mash up your data before it gets loaded into your Power BI data model. It's all about getting the data ready.
• DAX Language: Used for data analysis. You write DAX formulas and measures on data that has already been loaded into your model. It's for creating calculations, aggregates, and advanced analytics on top of your clean data.

In short: You shape your data with M, and then you analyze it with DAX.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Getting Familiar with the Power Query Editor

You can't write M without getting into the Power Query Editor. This is your workshop for all things data transformation. Getting there is easy.

In Power BI Desktop, navigate to the Home tab on the Ribbon and click the Transform Data button. This will launch a new window: the Power Query Editor. It's here that M code is generated and can be manually edited.

Understanding the Core Structure of M Code

At first glance, M code might look intimidating, but its structure is straightforward and follows a consistent pattern. Nearly every M query you encounter is built around the let and in expressions.

Here's the basic anatomy:

let Step1 = // some operation, like connecting to a data source Step2 = // a transformation applied to Step1's result Step3 = // a further transformation on Step2's result in Step3

The let Expression

The let block is where all the data transformation steps are defined. You can think of it as a series of variable assignments, where each variable holds the result of a specific operation. In Power Query, these variables are called "steps."

Each new step typically references the previous step, creating a sequential data transformation pipeline. For example, Step2 takes the output table from Step1 and performs an action on it. This creates a clear trail of how your data is being manipulated from its raw form to its final state.

The in Expression

The in block is very simple: it specifies what the query's final output should be. In most cases, this will be the name of the very last step defined in the let block, which contains the fully transformed table of data you want to load into your Power BI model.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Putting It All Together: A Simple Example

Let's look at a basic query that connects to an Excel file and changes a column's data type. Every action you take in the Power Query user interface creates a new step in the Applied Steps panel on the right.

If you click on Advanced Editor in the Home tab, you'll see the full M script for this query, which might look something like this:

let Source = Excel.Workbook(File.Contents("C:\Users\YourName\Documents\SalesData.xlsx"), null, true), Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"OrderID", Int64.Type}, {"OrderDate", type date}, {"Revenue", Currency.Type}}) in #"Changed Type"

Here's what each step does:

• Source: Connects to the Excel workbook.
• Sales_Sheet: Navigates to the specific sheet named "Sales".
• #"Promoted Headers": Takes the first row of the sheet and uses it as the table's column headers.
• #"Changed Type": Changes the data types for the OrderID, OrderDate, and Revenue columns.

Finally, in #"Changed Type" tells Power BI that the result of the final step is what it should load.

Common M Functions with Practical Examples

Learning M becomes much easier once you start to recognize common functions. Here are a few essential functions grouped by category, with examples you might use in a marketing or sales context.

1. Text Functions

These are incredibly useful for cleaning and reshaping text data, like product names, customer information, or campaign strings.

• Text.Split(text as text, separator as text): Splits a text value into a list of text values based on a specified delimiter.
• Text.Combine(texts as list, optional separator as nullable text): Joins a list of text values into a single text value.
• Text.Proper(text as nullable text): Capitalizes the first letter of each word in a text string. Great for cleaning up names.

2. Table Functions

These are the workhorses of M. They allow you to add, remove, filter, group, and sort your data tables.

• Table.SelectRows(table as table, condition as function): Filters a table based on a condition, keeping only the rows that evaluate to true.
• Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type): Adds a new column to the table.
• Table.Group(table as table, key as any, aggregatedColumns as list): Groups rows by a key column and aggregates other columns.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

3. Date Functions

Working with dates is a constant in data analysis. M has a rich library of functions for this.

• Date.Year(date as any) / Date.MonthName(date as any): Extracts a specific component from a date value.
• Date.DaysInMonth(date as any): Returns the number of days in the month for a given date.

Where to View and Edit M Code

You can see and edit the M script in two main places within the Power Query Editor:

1. The Formula Bar: Just like in Excel, there's a formula bar at the top of the editor. When you select a specific step in the "Applied Steps" panel on the right, the Formula Bar shows you the M code for just that single step. This is perfect for making quick edits to one part of your query. If you don't see it, go to the View tab and check the box for "Formula Bar."
2. The Advanced Editor: Found on both the Home and View tabs, the Advanced Editor opens a new window that shows the entire M script for the selected query, including the let...in structure. This is where you go to see the full picture, manually write complex logic, add comments, or reorder steps.

Best Practices for Working with M

As you get more comfortable, following a few best practices will make your queries more readable, maintainable, and easier to debug.

• Rename Your Steps: Power Query gives steps generic names like #"Changed Type" or #"Filtered Rows1". Right-click on a step and rename it to something descriptive, like Set_Data_Types or Filter_Out_Test_Accounts. This makes the logic far easier to follow for you and your teammates.
• Add Comments: In the Advanced Editor, you can add comments to explain your logic. A single-line comment starts with // and a multi-line comment is enclosed in /* ... */. Use this to document why you made a particular transformation.
• Build Iteratively: Don't try to write a huge, complex M script all at once. Use the interface to generate the base of your query one step at a time. Check the results in the data preview window after each step to ensure it's doing what you expect. Then, go into the Advanced Editor to refine a step if needed.

Final Thoughts

Understanding the M language is the key to unlocking the full data preparation power of Power BI. While the point-and-click interface is excellent, being able to read, edit, and write M code allows you to perform complex transformations that are impossible to do otherwise. By getting comfortable with its let...in structure and a handful of common functions, you can gain complete control over your data shaping process.

Manually preparing data, even with powerful tools like M, can be tedious, especially when you're pulling information from a dozen different marketing and sales platforms. We built Graphed to eliminate that struggle. Our platform connects directly to your data sources - like Google Analytics, Shopify, Facebook Ads, Salesforce, and HubSpot - and automates the entire reporting process. Instead of spending hours in an editor, you can ask for the dashboard you need in plain English and get real-time visualizations in seconds, all without writing a single line of code.