Power Query M Language: Beginner to Pro Guide

Most Power BI developers use Power Query through the UI — dragging, clicking, applying steps — without ever opening the Advanced Editor. That's fine for simple transformations. But once you need conditional logic, custom functions, loops, or error handling, you need to write M.

This guide covers M from first principles to production-level patterns I use on real client projects. No experience required.

Where Does M Run?

M runs in Power Query — available in Power BI Desktop, Excel (Get & Transform), Dataflows, and Azure Data Factory. Everything in this guide applies to all four.

1. What Is M Language?

M (officially the Power Query Formula Language) is a functional, case-sensitive, lazy-evaluated language designed for data transformation. Every step you create in the Power Query UI generates M code behind the scenes — the Advanced Editor just lets you read and edit it directly.

Key characteristics:

Functional — everything is an expression that returns a value
Case-sensitive — Text.Upper ≠ text.upper
Lazy evaluation — only computes what's needed for the final output
Immutable — values aren't mutated; each step creates a new value

2. The let…in Structure

Every M query follows the same basic structure: a let block that defines named steps, and an in statement that specifies which step to return.

M Language — Basic Structure

let
    // Step 1: connect to source
    Source = Csv.Document(
        File.Contents("C:\Data\Sales.csv"),
        [Delimiter=",", Encoding=65001]
    ),

    // Step 2: promote first row to headers
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    // Step 3: set correct data types
    ChangedTypes = Table.TransformColumnTypes(PromotedHeaders, {
        {"SaleDate",   type date},
        {"Amount",     type number},
        {"CustomerID", type text}
    }),

    // Step 4: filter out cancelled orders
    FilteredRows = Table.SelectRows(ChangedTypes, each [Status] <> "Cancelled")

in
    FilteredRows  // This is what Power Query returns

Step Names Are Just Variables

Each step name in let is simply a variable. You can name them anything meaningful. The last step before in doesn't have to be the one returned — in determines the output.

3. M Data Types

M has a rich type system. Understanding it prevents 80% of transformation errors:

Type	M Syntax	Example Value
Text	`type text`	`"Ankit Kumar"`
Number	`type number`	`42.5`
Integer	`Int64.Type`	`100`
Date	`type date`	`#date(2025,1,15)`
DateTime	`type datetime`	`#datetime(2025,1,15,9,0,0)`
Boolean	`type logical`	`true` / `false`
Null	`type null`	`null`
List	`type list`	`{1, 2, 3}`
Record	`type record`	`[Name="Ankit", City="Delhi"]`
Table	`type table`	A full table

4. Essential M Functions

Text Functions

M — Text Functions

// Case conversion
Text.Upper("ankit")          // "ANKIT"
Text.Lower("ANKIT")          // "ankit"
Text.Proper("ankit kumar")   // "Ankit Kumar"

// Trimming
Text.Trim("  hello  ")      // "hello"
Text.Clean(someText)        // removes non-printable characters

// Extraction
Text.Start("PROD-001", 4)   // "PROD"
Text.End("PROD-001", 3)     // "001"
Text.Middle("PROD-001", 5, 3) // "001" (start at 5, take 3)
Text.Length("etlguru")       // 7

// Split and Combine
Text.Split("a,b,c", ",")   // {"a","b","c"}
Text.Combine({"a","b"}, "-") // "a-b"

// Contains / Replace
Text.Contains("etlguru", "ETL") // true
Text.Replace("Hello World", "World", "M") // "Hello M"

Date & Time Functions

M — Date Functions

// Extract components
Date.Year([SaleDate])          // 2025
Date.Month([SaleDate])         // 1
Date.Day([SaleDate])           // 15
Date.DayOfWeekName([SaleDate]) // "Wednesday"
Date.MonthName([SaleDate])     // "January"
Date.WeekOfYear([SaleDate])    // 3

// Arithmetic
Date.AddDays([SaleDate], 7)    // 7 days later
Date.AddMonths([SaleDate], 1)  // next month
Duration.Days(Date.From(DateTime.LocalNow()) - [SaleDate])
// days since sale

// Start and End of periods (great for fiscal quarters)
Date.StartOfMonth([SaleDate])  // first day of month
Date.EndOfMonth([SaleDate])    // last day of month
Date.StartOfQuarter([SaleDate])// first day of quarter

Table Functions

M — Table Functions

// Filter rows
Table.SelectRows(Source, each [Amount] > 1000)

// Select columns (keep only these)
Table.SelectColumns(Source, {"SaleDate", "Amount", "Customer"})

// Remove columns
Table.RemoveColumns(Source, {"TempCol1", "TempCol2"})

// Rename columns
Table.RenameColumns(Source, {{"Amt", "Amount"}, {"Cust", "Customer"}})

// Add custom column
Table.AddColumn(Source, "Margin", each [Revenue] - [COGS], type number)

// Group by (aggregate)
Table.Group(Source, {"Region"}, {
    {"Total Revenue", each List.Sum([Amount]),     type number},
    {"Order Count",   each Table.RowCount(_), Int64.Type}
})

// Join two tables (like SQL JOIN)
Table.NestedJoin(
    Sales,    {"ProductID"},
    Products, {"ID"},
    "ProductDetails",
    JoinKind.LeftOuter
)

5. Conditional Logic (if / else)

M — Conditional Columns

// Basic if/else
Table.AddColumn(Source, "SizeCategory", each
    if [Amount] >= 100000 then "Large"
    else if [Amount] >= 25000 then "Medium"
    else "Small"
)

// Nested logic with null check
Table.AddColumn(Source, "Status", each
    if [DeliveryDate] = null then "Pending"
    else if [DeliveryDate] <= [OrderDate] then "On Time"
    else "Late"
)

// Using try...otherwise for error-safe conversion
Table.AddColumn(Source, "SafeAmount", each
    try Number.From([RawAmount]) otherwise 0
)

6. Custom Functions — The Real Power of M

This is where most tutorials stop — and where M becomes genuinely powerful. A custom function in M lets you apply the same transformation to any number of files, sheets, or API pages dynamically.

Simple Reusable Function

M — Custom Function

// Define the function (save as a new query named "fnCleanSales")
let
    CleanSalesFile = (FilePath as text) as table =>
    let
        Source    = Csv.Document(File.Contents(FilePath), [Delimiter=","]),
        Headers   = Table.PromoteHeaders(Source),
        TypedCols = Table.TransformColumnTypes(Headers, {
                        {"Date",   type date},
                        {"Amount", type number}
                    }),
        Filtered  = Table.SelectRows(TypedCols, each [Amount] <> null)
    in
        Filtered
in
    CleanSalesFile

Combining All Files in a Folder

The most common use: load every CSV or Excel file in a folder, apply the same cleaning function to each, and combine into one table.

M — Folder Combine Pattern

let
    // List all files in the folder
    FolderContents = Folder.Files("C:\Data\Monthly Sales\"),

    // Keep only CSV files
    CSVFiles = Table.SelectRows(FolderContents,
                   each Text.EndsWith([Name], ".csv")),

    // Apply our cleaning function to each file
    TransformedFiles = Table.AddColumn(CSVFiles, "Data",
                           each fnCleanSales([Folder Path] & [Name])),

    // Remove all columns except the Data column + file Name
    KeepCols = Table.SelectColumns(TransformedFiles, {"Name", "Data"}),

    // Expand the nested table into one flat table
    Expanded = Table.ExpandTableColumn(KeepCols, "Data",
                   Table.ColumnNames(KeepCols{0}[Data]))
in
    Expanded

Pro Use Case: API Pagination

The same folder-combine pattern works for paginated REST APIs. Write a function that takes a page number, call it with List.Generate to loop through all pages, then combine all results. This handles any pagination limit without manual steps.

7. Error Handling

M — Error Handling Patterns

// try...otherwise: return fallback on any error
Table.AddColumn(Source, "ParsedDate", each
    try Date.From([RawDate]) otherwise null
)

// try with error record inspection
Table.AddColumn(Source, "Result", each
    let
        AttemptedValue = try Number.From([Input])
    in
        if AttemptedValue[HasError]
        then "Error: " & AttemptedValue[Error][Message]
        else AttemptedValue[Value]
)

// Remove error rows from a column
Table.RemoveRowsWithErrors(Source, {"Amount", "Date"})

// Replace errors in a column with a default value
Table.ReplaceErrorValues(Source, {{"Amount", 0}, {"Date", null}})

8. List Operations

M — List Operations

// Create a list
MyList = {1, 2, 3, 4, 5}

// List of numbers (like Excel sequence)
Sequence = {1..100}

// Aggregations
List.Sum(MyList)    // 15
List.Average(MyList) // 3
List.Max(MyList)    // 5
List.Min(MyList)    // 1
List.Count(MyList)  // 5

// Transform every item (like Excel array formula)
List.Transform(MyList, each _ * 2)   // {2,4,6,8,10}

// Filter items
List.Select(MyList, each _ > 3)     // {4,5}

// Get column values as a list (useful for lookups)
Table.Column(Source, "ProductID")

// Check if a value is in a list
List.Contains({"A","B","C"}, [Category])  // true/false

// Generate a list with logic (like a loop)
List.Generate(
    () => 1,              // initial value
    each _ <= 10,         // condition
    each _ + 1,          // next value
    each _ * _            // selector (optional output transform)
)  // {1,4,9,16,25,36,49,64,81,100}

9. Performance Tips

M is lazy-evaluated, which helps — but there are still patterns that hurt performance:

Filter as early as possible. Every row you remove early is one less row to process in all subsequent steps. Put Table.SelectRows before joins and transformations.
Remove unused columns early. Use Table.SelectColumns right after the source step. Fewer columns = less memory = faster processing.
Avoid Table.AddColumn inside loops. If you need to add many columns, Table.TransformColumns and Table.FromRecords are faster patterns.
Use Query Folding. When connected to SQL Server or other databases, Power Query can "fold" M transformations back into SQL — running them at the source instead of locally. Check the query plan by right-clicking a step and looking for "View Native Query".
Avoid Table.Buffer unless needed. Buffering forces evaluation immediately and can slow things down — only use it when downstream steps reference a table multiple times in ways that prevent folding.

Query Folding Killer

Adding a custom column that uses M functions (like Text.Upper) immediately breaks query folding for that step and all steps after it. If folding matters, do text transformations in SQL before bringing data into Power Query.

10. Real-World Pattern: Dynamic Date Table in M

If you don't have a SQL Server date dimension, here's a complete date table built entirely in M — dynamic to today's date:

M — Dynamic Date Dimension

let
    StartDate  = #date(2020, 1, 1),
    EndDate    = Date.From(DateTime.LocalNow()),
    DayCount   = Duration.Days(EndDate - StartDate) + 1,

    DateList   = List.Dates(StartDate, DayCount, #duration(1,0,0,0)),
    ToTable    = Table.FromList(DateList, Splitter.SplitByNothing()),
    Renamed    = Table.RenameColumns(ToTable, {{"Column1", "Date"}}),
    SetType    = Table.TransformColumnTypes(Renamed, {{"Date", type date}}),

    AddYear    = Table.AddColumn(SetType,  "Year",       each Date.Year([Date]),         Int64.Type),
    AddMonth   = Table.AddColumn(AddYear,  "Month",      each Date.Month([Date]),        Int64.Type),
    AddMonName = Table.AddColumn(AddMonth, "MonthName",  each Date.MonthName([Date]),    type text),
    AddQtr     = Table.AddColumn(AddMonName,"Quarter",    each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
    AddWeek    = Table.AddColumn(AddQtr,   "WeekNum",    each Date.WeekOfYear([Date]),   Int64.Type),
    AddDayName = Table.AddColumn(AddWeek,  "DayName",    each Date.DayOfWeekName([Date]),type text),
    AddIsWkend = Table.AddColumn(AddDayName,"IsWeekend",  each Date.DayOfWeek([Date]) >= 5,   type logical),

    // Fiscal year (April start — adjust as needed)
    AddFiscYr  = Table.AddColumn(AddIsWkend, "FiscalYear",
                     each if Date.Month([Date]) >= 4
                          then Date.Year([Date]) + 1
                          else Date.Year([Date]), Int64.Type),

    DateKey    = Table.AddColumn(AddFiscYr, "DateKey",
                     each Int32.From(Text.From(Date.Year([Date]))
                          & Text.PadStart(Text.From(Date.Month([Date])),2,"0")
                          & Text.PadStart(Text.From(Date.Day([Date])),2,"0")), Int64.Type)
in
    DateKey

Ankit Kumar

Power Query Expert · Power BI Developer · etlguru

Ankit has built M-language solutions for folder automation, API ingestion, and complex ETL pipelines on 25+ Power BI projects across manufacturing and e-commerce. Founder of Pyivot Solutions.

About LinkedIn Contact

Power Query M Language: The Complete Beginner-to-Pro Guide

1. What Is M Language?

2. The let…in Structure

3. M Data Types

4. Essential M Functions

Text Functions

Date & Time Functions

Table Functions

5. Conditional Logic (if / else)

6. Custom Functions — The Real Power of M

Simple Reusable Function

Combining All Files in a Folder

7. Error Handling

8. List Operations

9. Performance Tips

10. Real-World Pattern: Dynamic Date Table in M

Ankit Kumar

Related Articles

Build a Complete Power BI Sales Dashboard

10 SQL Optimization Tricks Every Analyst Must Know

Browse All Articles