M Language (Power Query) Complete Guide: Every Function You Need, Text, Date, Number, Table Operations, Error Handling, Custom Functions, and Real-World Patterns

Every click you make in Dataflow Gen2 generates M code. Filter a column — M code. Merge two tables — M code. Add a custom column — M code. Most data engineers never look at the M code because the visual UI handles everything. But when the UI cannot do what you need — conditional logic, regex extraction, complex date arithmetic, custom functions — knowing M is the difference between “I cannot do that in Dataflow Gen2” and “Done.”

Think of the Dataflow Gen2 visual UI like cruise control in a car — it handles the common scenarios (filter, join, sort) automatically. M language is switching to manual — you control exactly what happens, including things cruise control cannot do (custom formulas, error handling, dynamic logic). Same car, more control.

Table of Contents

• What Is M Language?
• The let…in Structure
• Data Types in M
• Text Functions
• Number Functions
• Date and Time Functions
• Logical Functions
• List Functions
• Table Functions
• Filtering Rows
• Adding and Transforming Columns
• Removing and Renaming Columns
• Sorting and Grouping
• Joining (Merging) Tables
• Appending (Combining) Tables
• Pivoting and Unpivoting
• Buffering for Performance
• Record Functions
• Type Conversion Functions
• Error Handling
• Custom Functions
• Conditional Logic (if…then…else)
• Query Folding: When M Pushes Down to the Source
• Real-World M Patterns
• Pattern 1: Dynamic Date Filter
• Pattern 2: Clean Phone Numbers
• Pattern 3: Split Full Name into First and Last
• Pattern 4: Fiscal Year Calculation
• Pattern 5: Conditional Column with Multiple Rules
• Viewing and Editing M Code in Dataflow Gen2
• Common Mistakes
• Interview Questions
• Wrapping Up

What Is M Language?

M (informally “Power Query Formula Language”) is the functional language behind Power Query and Dataflow Gen2. Every visual transformation step generates M code. You can view, edit, and write M directly in the Advanced Editor.

The let…in Structure

Every M query follows this structure:

let
    // Step 1: Define the source
    Source = Sql.Database("server.database.windows.net", "SalesDB"),

    // Step 2: Navigate to the table
    SalesTable = Source{[Schema="dbo", Item="Orders"]}[Data],

    // Step 3: Filter rows
    FilteredRows = Table.SelectRows(SalesTable, each [Amount] > 100),

    // Step 4: Add a calculated column
    AddedColumn = Table.AddColumn(FilteredRows, "Tax", each [Amount] * 0.13, type number),

    // Step 5: Remove unnecessary columns
    RemovedColumns = Table.RemoveColumns(AddedColumn, {"InternalCode", "TempFlag"})
in
    // Return the final step
    RemovedColumns

Each step references the previous step by name. The in clause returns the final result.

Data Types in M

type text          → "Hello"
type number        → 42, 3.14
type logical       → true, false
type date          → #date(2026, 6, 5)
type time          → #time(14, 30, 0)
type datetime      → #datetime(2026, 6, 5, 14, 30, 0)
type datetimezone  → #datetimezone(2026, 6, 5, 14, 30, 0, -5, 0)
type duration      → #duration(5, 3, 0, 0)   → 5 days, 3 hours
type null          → null
type list          → {1, 2, 3, "a", "b"}
type record        → [Name = "Naveen", Age = 30]
type table         → Table.FromRecords({[A=1, B=2], [A=3, B=4]})

Text Functions

// Case conversion
Text.Upper("hello world")                → "HELLO WORLD"
Text.Lower("HELLO WORLD")                → "hello world"
Text.Proper("hello world")               → "Hello World"

// Trimming
Text.Trim("  hello  ")                   → "hello"
Text.TrimStart("  hello")                → "hello"
Text.TrimEnd("hello  ")                  → "hello"

// Substring
Text.Start("Hello World", 5)             → "Hello"
Text.End("Hello World", 5)               → "World"
Text.Middle("Hello World", 6, 5)         → "World"
Text.Range("Hello World", 0, 5)          → "Hello"

// Search and contains
Text.Contains("Hello World", "World")    → true
Text.StartsWith("Hello", "He")           → true
Text.EndsWith("Hello", "lo")             → true
Text.PositionOf("Hello World", "World")  → 6

// Replace and remove
Text.Replace("Hello World", "World", "Fabric")  → "Hello Fabric"
Text.Remove("Phone: 416-555-1234", {"(", ")", "-", " "})  → "Phone:4165551234"

// Split and combine
Text.Split("A,B,C,D", ",")              → {"A", "B", "C", "D"}
Text.Combine({"A", "B", "C"}, ", ")      → "A, B, C"
Text.Combine({"A", "B", "C"}, " - ")     → "A - B - C"

// Length and padding
Text.Length("Hello")                      → 5
Text.PadStart("42", 5, "0")              → "00042"
Text.PadEnd("Hi", 10, ".")               → "Hi........"

// Repeat
Text.Repeat("Ha", 3)                     → "HaHaHa"

// Clean (remove non-printable characters)
Text.Clean("Hello" & Character.FromNumber(0) & "World")  → "HelloWorld"

// Extract with pattern (regex-like)
Text.BeforeDelimiter("john.doe@email.com", "@")    → "john.doe"
Text.AfterDelimiter("john.doe@email.com", "@")     → "email.com"
Text.BetweenDelimiters("<tag>value</tag>", "<tag>", "</tag>") → "value"

Number Functions

// Rounding
Number.Round(3.456, 2)                   → 3.46
Number.RoundDown(3.9)                    → 3
Number.RoundUp(3.1)                      → 4
Number.RoundAwayFromZero(-3.5)           → -4

// Math
Number.Abs(-42)                          → 42
Number.Power(2, 10)                      → 1024
Number.Sqrt(144)                         → 12
Number.Mod(10, 3)                        → 1
Number.Log(100)                          → 2 (log base 10)
Number.Ln(2.718)                         → ~1 (natural log)

// Min/Max
Number.Min(5, 3)                         → 3
Number.Max(5, 3)                         → 5

// Type conversion
Number.From("42")                        → 42
Number.FromText("3.14")                  → 3.14
Number.ToText(42)                        → "42"
Number.ToText(3.14, "0.00")              → "3.14"

// Random
Number.Random()                          → random between 0 and 1
Number.RandomBetween(1, 100)             → random integer 1-100

Date and Time Functions

// Current
DateTime.LocalNow()                      → current local datetime
DateTime.FixedLocalNow()                 → snapshot (does not change during refresh)
Date.From(DateTime.LocalNow())           → today's date

// Constructing
#date(2026, 6, 5)                        → June 5, 2026
#time(14, 30, 0)                         → 2:30 PM
#datetime(2026, 6, 5, 14, 30, 0)         → June 5, 2026 2:30 PM

// Extracting parts
Date.Year(#date(2026, 6, 5))             → 2026
Date.Month(#date(2026, 6, 5))            → 6
Date.Day(#date(2026, 6, 5))              → 5
Date.DayOfWeek(#date(2026, 6, 5))        → 4 (Thursday, 0=Sunday)
Date.DayOfYear(#date(2026, 6, 5))        → 156
Date.QuarterOfYear(#date(2026, 6, 5))    → 2
Date.WeekOfYear(#date(2026, 6, 5))       → 23
Date.DayOfWeekName(#date(2026, 6, 5))    → "Thursday"
Date.MonthName(#date(2026, 6, 5))        → "June"

// Arithmetic
Date.AddDays(#date(2026, 6, 5), 7)       → June 12, 2026
Date.AddMonths(#date(2026, 6, 5), -3)    → March 5, 2026
Date.AddYears(#date(2026, 6, 5), 1)      → June 5, 2027
Date.AddWeeks(#date(2026, 6, 5), 2)      → June 19, 2026

// Start/End of periods
Date.StartOfMonth(#date(2026, 6, 15))    → June 1, 2026
Date.EndOfMonth(#date(2026, 6, 15))      → June 30, 2026
Date.StartOfQuarter(#date(2026, 6, 15))  → April 1, 2026
Date.EndOfQuarter(#date(2026, 6, 15))    → June 30, 2026
Date.StartOfYear(#date(2026, 6, 15))     → January 1, 2026
Date.StartOfWeek(#date(2026, 6, 5))      → June 1, 2026 (Monday)

// Difference
Duration.Days(#date(2026, 12, 31) - #date(2026, 1, 1))  → 364
Duration.TotalHours(#datetime(2026,6,5,14,0,0) - #datetime(2026,6,5,8,0,0))  → 6

// Type conversion
Date.From("2026-06-05")                  → #date(2026, 6, 5)
Date.ToText(#date(2026, 6, 5), "yyyy-MM-dd")  → "2026-06-05"
Date.ToText(#date(2026, 6, 5), "MMMM d, yyyy") → "June 5, 2026"

// Checks
Date.IsInCurrentMonth(#date(2026, 6, 5)) → true (if current month is June)
Date.IsInPreviousMonth(someDate)         → true/false
Date.IsLeapYear(#date(2024, 1, 1))       → true

Logical Functions

// If-then-else (inline)
if [Amount] > 1000 then "High" else "Low"

// Nested
if [Amount] > 10000 then "Premium"
else if [Amount] > 1000 then "Standard"
else "Basic"

// Logical operators
[Age] > 18 and [Country] = "Canada"       → both must be true
[Status] = "Active" or [Status] = "Trial" → either can be true
not [IsDeleted]                            → negation

// Null handling
if [Email] = null then "No Email" else [Email]
[Email] ?? "No Email"                      → null coalescing (same result)

List Functions

// Create
{1, 2, 3, 4, 5}                          → list literal
{1..10}                                    → {1, 2, 3, ..., 10}
{"A", "B", "C"}                           → text list

// Access
List.First({10, 20, 30})                  → 10
List.Last({10, 20, 30})                   → 30
{10, 20, 30}{1}                           → 20 (0-indexed)

// Aggregate
List.Sum({1, 2, 3, 4, 5})                → 15
List.Average({1, 2, 3, 4, 5})            → 3
List.Min({5, 3, 8, 1})                   → 1
List.Max({5, 3, 8, 1})                   → 8
List.Count({1, 2, 3})                    → 3

// Filter and transform
List.Select({1, 2, 3, 4, 5}, each _ > 3) → {4, 5}
List.Transform({1, 2, 3}, each _ * 10)    → {10, 20, 30}
List.Distinct({1, 1, 2, 2, 3})           → {1, 2, 3}
List.Sort({3, 1, 2})                     → {1, 2, 3}
List.Reverse({1, 2, 3})                  → {3, 2, 1}

// Check
List.Contains({1, 2, 3}, 2)             → true
List.IsEmpty({})                         → true
List.AnyTrue({false, true, false})       → true
List.AllTrue({true, true, true})         → true

Table Functions

Filtering Rows

// Keep rows matching condition
Table.SelectRows(table, each [Status] = "Active")
Table.SelectRows(table, each [Amount] > 1000 and [Country] = "Canada")
Table.SelectRows(table, each Text.Contains([Name], "Inc"))

// Remove rows matching condition
Table.SelectRows(table, each [Status] <> "Deleted")

// First/last N rows
Table.FirstN(table, 100)                  → first 100 rows
Table.LastN(table, 50)                    → last 50 rows
Table.Skip(table, 10)                     → skip first 10 rows

// Remove duplicates
Table.Distinct(table)                     → all columns
Table.Distinct(table, {"Email"})          → distinct by Email only

// Remove nulls
Table.SelectRows(table, each [Email] <> null)

Adding and Transforming Columns

// Add custom column
Table.AddColumn(table, "FullName", each [FirstName] & " " & [LastName], type text)
Table.AddColumn(table, "Tax", each [Amount] * 0.13, type number)
Table.AddColumn(table, "Year", each Date.Year([OrderDate]), Int64.Type)

// Transform existing column
Table.TransformColumns(table, {{"Name", Text.Proper, type text}})
Table.TransformColumns(table, {{"Email", Text.Lower, type text}})
Table.TransformColumns(table, {
    {"Name", Text.Proper, type text},
    {"Email", Text.Lower, type text},
    {"Phone", each Text.Remove(_, {"-", " ", "(", ")"}), type text}
})

// Replace values
Table.ReplaceValue(table, null, "Unknown", Replacer.ReplaceValue, {"City"})
Table.ReplaceValue(table, "NY", "New York", Replacer.ReplaceText, {"State"})

// Change column type
Table.TransformColumnTypes(table, {
    {"Amount", type number},
    {"OrderDate", type date},
    {"IsActive", type logical}
})

Removing and Renaming Columns

// Remove
Table.RemoveColumns(table, {"TempCol", "InternalID"})
Table.SelectColumns(table, {"Name", "Email", "City"})  → keep only these

// Rename
Table.RenameColumns(table, {{"OldName", "NewName"}, {"old_col", "new_col"}})

Sorting and Grouping

// Sort
Table.Sort(table, {{"Amount", Order.Descending}})
Table.Sort(table, {{"Country", Order.Ascending}, {"Amount", Order.Descending}})

// Group By
Table.Group(table, {"Department"}, {
    {"HeadCount", each Table.RowCount(_), Int64.Type},
    {"TotalSalary", each List.Sum([Salary]), type number},
    {"AvgSalary", each List.Average([Salary]), type number},
    {"MaxSalary", each List.Max([Salary]), type number}
})

Joining (Merging) Tables

// Inner join
Table.NestedJoin(orders, {"CustomerID"}, customers, {"CustomerID"}, "Cust", JoinKind.Inner)

// Left outer join
Table.NestedJoin(orders, {"CustomerID"}, customers, {"CustomerID"}, "Cust", JoinKind.LeftOuter)

// After join, expand the nested column:
Table.ExpandTableColumn(previousStep, "Cust", {"Name", "Email"}, {"CustomerName", "CustomerEmail"})

// All join types:
JoinKind.Inner           → matching rows only
JoinKind.LeftOuter       → all left + matching right
JoinKind.RightOuter      → all right + matching left
JoinKind.FullOuter       → all rows from both
JoinKind.LeftAnti        → left rows with NO match in right
JoinKind.RightAnti       → right rows with NO match in left

Appending (Combining) Tables

// Stack tables vertically (UNION)
Table.Combine({table1, table2, table3})

Pivoting and Unpivoting

// Pivot (rows → columns)
Table.Pivot(table, List.Distinct(table[Quarter]), "Quarter", "Revenue", List.Sum)

// Unpivot (columns → rows)
Table.UnpivotOtherColumns(table, {"Product"}, "Quarter", "Revenue")
Table.Unpivot(table, {"Q1", "Q2", "Q3", "Q4"}, "Quarter", "Revenue")

Buffering for Performance

// Buffer a small table in memory (improves merge performance)
Table.Buffer(smallLookupTable)

// Use before a merge/join with a small lookup table:
let
    LookupBuffered = Table.Buffer(LookupTable),
    Merged = Table.NestedJoin(MainTable, {"Key"}, LookupBuffered, {"Key"}, "Lookup", JoinKind.LeftOuter)
in
    Merged

Error Handling

// try...otherwise (like try/catch)
try Number.From("not a number") otherwise 0          → 0
try Date.From("bad date") otherwise null              → null
try [Amount] / [Quantity] otherwise 0                 → handles divide by zero

// Replace all errors in a column
Table.ReplaceErrorValues(table, {{"Amount", 0}, {"Date", null}})

// Check for errors
try [SomeColumn]    → returns [HasError=false, Value=...] or [HasError=true, Error=...]

Custom Functions

// Define a reusable function
let
    // Custom function: clean a phone number
    CleanPhone = (phone as text) as text =>
        let
            cleaned = Text.Remove(phone, {"(", ")", "-", " ", "+"}),
            result = if Text.Length(cleaned) = 10 then cleaned
                     else if Text.Length(cleaned) = 11 and Text.StartsWith(cleaned, "1")
                          then Text.End(cleaned, 10)
                     else cleaned
        in result,

    // Use the function
    Source = Lakehouse.Tables("bronze_lakehouse"),
    Customers = Source{[Name="customers"]}[Data],
    CleanedPhones = Table.TransformColumns(Customers, {{"Phone", CleanPhone, type text}})
in
    CleanedPhones

Query Folding: When M Pushes Down to the Source

FOLDED (fast): Filter sent to SQL Server → only matching rows transferred
  Table.SelectRows → generates: SELECT * FROM Orders WHERE Amount > 1000

NOT FOLDED (slow): ALL rows downloaded → M filters locally
  Happens when: custom M functions, complex text operations, or
  transformations the source database cannot translate to SQL

Check folding: Right-click a step → "View Native Query"
  If grayed out → NOT folded (step breaks folding)

Rule: Put foldable steps (filter, select columns, sort) BEFORE non-foldable steps (custom columns, M functions) to maximize what gets pushed to the source.

Real-World M Patterns

Pattern 1: Dynamic Date Filter

// Filter to last 30 days (dynamic)
Table.SelectRows(table, each [OrderDate] >= Date.AddDays(Date.From(DateTime.LocalNow()), -30))

Pattern 2: Clean Phone Numbers

Table.AddColumn(table, "CleanPhone", each
    Text.Remove([Phone], {"(", ")", "-", " ", "+"}), type text)

Pattern 3: Split Full Name into First and Last

Table.AddColumn(table, "FirstName", each Text.BeforeDelimiter([FullName], " "), type text)
Table.AddColumn(previousStep, "LastName", each Text.AfterDelimiter([FullName], " "), type text)

Pattern 4: Fiscal Year Calculation

// Fiscal year starts April 1
Table.AddColumn(table, "FiscalYear", each
    if Date.Month([OrderDate]) >= 4
    then Date.Year([OrderDate])
    else Date.Year([OrderDate]) - 1, Int64.Type)

Pattern 5: Conditional Column with Multiple Rules

Table.AddColumn(table, "CustomerTier", each
    if [LifetimeRevenue] >= 100000 then "Platinum"
    else if [LifetimeRevenue] >= 50000 then "Gold"
    else if [LifetimeRevenue] >= 10000 then "Silver"
    else "Bronze", type text)

Viewing and Editing M Code in Dataflow Gen2

1. Open your Dataflow Gen2
2. Select any query in the left panel
3. Click Advanced Editor in the toolbar (or Home → Advanced Editor)
4. The full M code for that query is displayed
5. Edit directly → click Done → changes apply immediately

Every visual step (filter, merge, add column) appears as a line in the Advanced Editor.

Common Mistakes

1. Breaking query folding with early custom columns — put filters BEFORE custom columns. Custom M functions break folding, causing all data to download.

2. Not using Table.Buffer for small lookup tables — merging a large table with a small one without buffering causes repeated scans of the small table.

3. Hardcoding dates instead of dynamic — [Date] > #date(2026,1,1) breaks next year. Use Date.AddDays(DateTime.LocalNow(), -30) for dynamic filters.

4. Not handling nulls — M operations on null values produce errors. Use the ?? "default" null coalescing operator or explicit null checks.

5. Writing M when the UI works — the UI generates clean, foldable M. Only write M manually for patterns the UI cannot handle (custom functions, complex conditional logic, dynamic dates).

Interview Questions

Q: What is M language and where is it used? A: M is the functional language behind Power Query and Dataflow Gen2. Every visual transformation generates M code in a let…in structure. It includes functions for text, dates, numbers, tables (filter, join, pivot), lists, error handling, and custom functions. Data engineers use M for advanced transformations beyond what the Dataflow Gen2 visual UI offers.

Q: What is query folding and why does it matter? A: Query folding translates M steps into native source queries (SQL). A filter step becomes a WHERE clause — only matching rows are transferred. When folding breaks (custom M functions, complex text operations), ALL data is downloaded and processed locally. This can turn a 10-second query into a 10-minute one. Check folding by right-clicking a step and looking for “View Native Query.”

Wrapping Up

M language is the power behind every Dataflow Gen2 transformation. The visual UI handles 80% of scenarios by generating M code for you. This reference covers the other 20% — when you need custom functions, dynamic date filters, complex conditional logic, or advanced text processing. Bookmark this post and reference it whenever the UI is not enough.

Related posts: – Dataflow Gen2: Introduction – Dataflow Gen2: Advanced – Dataflow Gen2: Production – Fabric Connections & Gateways

---

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.