Navigating the M Standard Library: A Practical Reference to Built-In Functions, Namespaces, and When to Use Each

Introduction

You're three steps into building a Power Query transformation and you need to clean up a date column. You know M has something for this — you've seen it used before — but you can't quite remember whether it's Date.FromText or DateTime.From or something else entirely. So you start typing, scroll through the autocomplete list, pick something that looks right, and hope for the best. Sometimes it works. Sometimes you get a cryptic error message and spend twenty minutes debugging a problem that didn't need to exist.

This experience is almost universal among Power Query users who've moved past the point-and-click basics. The M standard library is enormous — over 700 built-in functions spanning dates, text, lists, tables, numbers, and more — and it's organized in a way that's actually quite logical once someone explains it to you. The problem isn't the library itself. The problem is that most people never get a proper map of the territory.

By the end of this lesson, you'll have that map. We're going to walk through how the M standard library is structured, what each major namespace covers, how to look up functions you don't know yet, and how to make smart decisions about which function to reach for in common data transformation scenarios. You won't memorize 700 functions. You'll learn to navigate them.

What you'll learn:

• How M organizes its built-in functions into namespaces and why that structure matters
• The purpose and key functions of the six most important namespaces: Text, Date, List, Table, Number, and Record
• How to use the #shared intrinsic keyword to explore the library from inside Power Query
• Decision frameworks for choosing between similar-sounding functions
• How to read M function documentation so you can teach yourself any function you don't already know

---

Prerequisites

You should be comfortable writing basic M expressions in the Advanced Editor and understand the concept of a let...in block. You should also know what tables, lists, and records are as M data types — even if you're a bit fuzzy on them, this lesson will reinforce that understanding.

---

How the M Standard Library Is Actually Organized

The M standard library isn't a flat list of functions. It's organized into namespaces — groups of related functions that share a common prefix before the dot. When you see Text.Upper, the Text part is the namespace and Upper is the function name within that namespace.

Think of namespaces like the departments in a hardware store. You don't memorize where every single product is. You know that plumbing supplies are in one section, electrical in another, and lumber in a third. When you need a pipe fitting, you walk to plumbing. The organization does the work of narrowing your search.

M's namespaces follow the same principle. Every function in the Date namespace operates on date values. Every function in the Text namespace operates on text strings. Once you internalize this, the library stops feeling like a wall of random functions and starts feeling like a well-labeled filing system.

Here's a quick orientation of the major namespaces:

Namespace	Works On	Example Functions
Text	Text strings	Text.Upper, Text.Split, Text.Contains
Date	Date values	Date.Year, Date.AddDays, Date.From
DateTime	Date + time values	DateTime.LocalNow, DateTime.From
Number	Numeric values	Number.Round, Number.From, Number.IsNaN
List	Lists (arrays)	List.Sum, List.Distinct, List.Transform
Table	Tables	Table.SelectRows, Table.AddColumn, Table.Group
Record	Records (key-value pairs)	Record.Field, Record.ToTable, Record.AddField
Logical	True/false values	Logical.From, Logical.ToText
Type	M type system	Type.Is, Value.Is
Binary	Binary data	Binary.From, Binary.ToText
Duration	Time durations	Duration.Days, Duration.From
Time	Time-of-day values	Time.Hour, Time.From

There are others (Splitter, Replacer, Combiner, Lines, Json, Xml, Csv...), but mastering the twelve above covers the vast majority of real-world Power Query work.

---

Exploring the Library with `#shared`

Before diving into each namespace, you need to know about a trick that most Power Query users never discover: you can browse the entire standard library from inside Power Query using a special keyword called #shared.

Open Power Query, create a blank query (in the Power Query Editor, go to Home → New Source → Other Sources → Blank Query), then open the Advanced Editor and type:

let
    Source = #shared
in
    Source

Run this query. What you get back is a Record containing every single built-in function, operator, and constant in the M environment — over 700 entries, each one a field in the record. You can click on any function name to see its type signature right there in the formula bar.

This is your live reference. Any time you want to explore what's available, #shared gives you a searchable index without ever leaving Power Query.

You can also filter it to explore a specific namespace. Here's how to pull out just the Text functions:

let
    AllFunctions = #shared,
    AsList = Record.ToTable(AllFunctions),
    TextOnly = Table.SelectRows(AsList, each Text.StartsWith([Name], "Text."))
in
    TextOnly

This converts #shared into a two-column table of names and values, then filters to rows where the function name starts with "Text.". You now have a browsable index of every Text function. Swap "Text." for "Date." or "List." and you've got those namespaces too.

Tip: Once you have the TextOnly table, you can click on any value in the Value column to see the function's type signature. It won't show you examples, but it will show you parameter names and types — which is often enough to understand how a function works.

---

The `Text` Namespace: Your Most-Used Toolkit

The Text namespace is where you'll spend a huge portion of your time in real data work. Source data is messy, and messy usually means messy text.

The functions cluster into a few natural groups:

Transformation: Change the content or shape of a string.

• Text.Upper / Text.Lower / Text.Proper — change casing
• Text.Trim / Text.TrimStart / Text.TrimEnd — remove whitespace
• Text.Replace — substitute one substring for another
• Text.PadStart / Text.PadEnd — pad a string to a fixed length

Extraction: Pull a piece of a string out.

• Text.Start(text, count) — first N characters
• Text.End(text, count) — last N characters
• Text.Middle(text, offset, count) — substring by position
• Text.BetweenDelimiters(text, startDelim, endDelim) — extract what's between two markers

Testing: Ask a question about a string and get true/false back.

• Text.Contains(text, substring) — does it contain this?
• Text.StartsWith / Text.EndsWith — positional containment
• Text.Length(text) — how many characters?

Splitting and Combining:

• Text.Split(text, delimiter) — split into a list
• Text.Combine(list, separator) — join a list into a string
• Text.SplitAny(text, separators) — split on any character in a set

Here's a realistic example. Suppose you have a column called [FullName] formatted as "SMITH, John R." and you need to extract just the last name, lowercase it, and combine it with the first name in "First Last" format:

let
    Source = /* your table */,
    SplitName = Table.AddColumn(Source, "NameParts", 
        each Text.Split([FullName], ", ")),
    LastName = Table.AddColumn(SplitName, "LastName",
        each Text.Proper([NameParts]{0})),
    FirstName = Table.AddColumn(LastName, "FirstName",
        each Text.Trim(Text.Split([NameParts]{1}, " "){0})),
    CleanName = Table.AddColumn(FirstName, "CleanName",
        each [FirstName] & " " & [LastName])
in
    CleanName

Notice how Text.Split returns a list, and you access list items by position using {0} (zero-indexed). This is a very common pattern in M text work.

---

The `Date`, `DateTime`, and `Duration` Namespaces

These three namespaces work together and it's important to understand the distinction between them:

• Date deals with calendar dates — year, month, day, no time component.
• DateTime deals with a date plus a time of day.
• Duration deals with a length of time (e.g., "47 days" or "3 hours").

Common Date functions:

Date.Year(#date(2024, 11, 15))       // Returns 2024
Date.Month(#date(2024, 11, 15))      // Returns 11
Date.Day(#date(2024, 11, 15))        // Returns 15
Date.DayOfWeek(#date(2024, 11, 15))  // Returns 4 (Friday, 0=Sunday)
Date.AddDays(#date(2024, 11, 15), 30) // Returns #date(2024, 12, 15)
Date.AddMonths(#date(2024, 11, 15), -3) // Returns #date(2024, 8, 15)
Date.EndOfMonth(#date(2024, 11, 15)) // Returns #date(2024, 11, 30)

Warning: Date.DayOfWeek uses a zero-based index starting on Sunday by default. So Monday = 1, Friday = 5. You can pass a Day.Monday enum as a second argument to change the starting day: Date.DayOfWeek(myDate, Day.Monday).

Type conversion is where beginners get tripped up. You'll encounter three conversion functions that sound similar:

• Date.From(value) — converts text, DateTime, or a number to a Date
• DateTime.From(value) — converts text or a number to a DateTime
• Date.FromText(text) — parses specifically from a text string using a known format

Use Date.From when you're not sure what type you're converting from — it's the most flexible. Use Date.FromText when you need to specify a format explicitly:

Date.FromText("15/11/2024", [Format="dd/MM/yyyy", Culture="en-GB"])

Duration for calculating differences:

let
    StartDate = #date(2024, 1, 1),
    EndDate = #date(2024, 11, 15),
    Gap = EndDate - StartDate,  // Returns a Duration value
    DaysGap = Duration.Days(Gap)  // Returns 319
in
    DaysGap

When you subtract two Date values, M returns a Duration. To get the number of days out of that, pass it through Duration.Days. Similarly, Duration.Hours, Duration.Minutes, and Duration.TotalDays (which handles fractional days) are essential for time calculations.

---

The `List` Namespace: Working with Collections

A list in M is an ordered sequence of values wrapped in curly braces: {1, 2, 3} or {"apple", "banana", "cherry"}. The List namespace is enormous because lists are M's fundamental collection type.

The most useful functions group naturally:

Aggregation: Turn a list into a single value.

List.Sum({10, 20, 30})       // 60
List.Average({10, 20, 30})   // 20
List.Max({10, 20, 30})       // 30
List.Min({10, 20, 30})       // 10
List.Count({10, 20, 30})     // 3

Selection and filtering:

List.Distinct({"a", "b", "a", "c"})     // {"a", "b", "c"}
List.Select({1,2,3,4,5}, each _ > 3)    // {4, 5}
List.First({"x", "y", "z"})             // "x"
List.Last({"x", "y", "z"})              // "z"
List.FirstN({"x", "y", "z"}, 2)         // {"x", "y"}

Transformation:

List.Transform({1, 2, 3}, each _ * 10)  // {10, 20, 30}
List.Sort({3, 1, 2})                     // {1, 2, 3}
List.Reverse({1, 2, 3})                  // {3, 2, 1}
List.Accumulate({1,2,3,4}, 0, (state, current) => state + current) // 10

Set operations:

List.Intersect({{1,2,3}, {2,3,4}})   // {2, 3}
List.Union({{1,2,3}, {2,3,4}})       // {1, 2, 3, 4}
List.Difference({1,2,3,4}, {2,4})    // {1, 3}

A real-world use: you have a sales table and want to check whether a given product category exists in the data before building a filter. Rather than adding a full filtered step, you can check the column's distinct values directly:

let
    Source = SalesTable,
    Categories = List.Distinct(Source[Category]),
    HasElectronics = List.Contains(Categories, "Electronics")
in
    HasElectronics

Tip: each _ > 3 is shorthand for (x) => x > 3. The underscore _ is the implicit parameter name when using each. You'll see this pattern constantly in List.Select, List.Transform, and similar higher-order functions.

---

The `Table` Namespace: Where Data Transformation Lives

The Table namespace is the heart of Power Query. Every time you filter rows, add a column, or reshape your data, you're using Table functions — even when the UI generates the code for you.

Adding and removing columns:

Table.AddColumn(myTable, "TaxAmount", each [Revenue] * 0.2, type number)
Table.RemoveColumns(myTable, {"TempColumn", "OldID"})
Table.RenameColumns(myTable, {{"OldName", "NewName"}, {"OldDate", "SaleDate"}})

Filtering rows:

// Keep only rows where Status is "Active"
Table.SelectRows(myTable, each [Status] = "Active")

// Remove rows where any column has a null
Table.SelectRows(myTable, each not List.AnyTrue(
    List.Transform(Record.ToList(_), each _ = null)
))

Structural transformations:

Table.Pivot(...)       // Rows to columns
Table.Unpivot(...)     // Columns to rows
Table.Group(...)       // Aggregate by group
Table.Join(...)        // Join two tables
Table.Sort(myTable, {{"SaleDate", Order.Descending}})

A function that trips up beginners is Table.TransformColumns. It looks like Table.AddColumn but it modifies existing columns rather than adding new ones:

// Round all values in "Price" to 2 decimal places
Table.TransformColumns(myTable, {{"Price", each Number.Round(_, 2), type number}})

The second argument is a list of lists — each inner list contains the column name, the transformation function, and optionally the output type. Getting this structure right is the key to using it correctly.

---

The `Record` Namespace: Key-Value Pair Operations

A record in M is a set of named fields, like a single row of a table: [Name = "Alice", Age = 34, Department = "Finance"]. You encounter records constantly — every row in a table is a record.

The most important Record functions:

// Get a field value by name (useful when the field name is dynamic)
Record.Field(myRecord, "Department")   // "Finance"

// Check if a field exists
Record.HasFields(myRecord, "Salary")   // false (it's not there)

// Add or modify a field
Record.AddField(myRecord, "Salary", 75000)
// Note: Record.AddField errors if the field already exists
// Use Record.TransformFields or rebuild the record for updates

// Remove a field
Record.RemoveFields(myRecord, {"Age"})

// Convert a record to a two-column table
Record.ToTable([Name="Alice", Age=34])
// Returns a table with columns "Name" and "Value"

One of the most powerful Record patterns is using records to do dynamic column lookups. Suppose you have a lookup table and you've already converted it to a record using Record.FromTable. Now you can do fast, direct lookups without joins:

let
    LookupTable = Table.FromRows({
        {"CA", "California"}, {"TX", "Texas"}, {"NY", "New York"}
    }, {"Code", "State"}),
    LookupRecord = Record.FromTable(LookupTable),
    // Later, in another step:
    StateName = Record.Field(LookupRecord, "TX")  // "Texas"
in
    StateName

---

The `Number` Namespace: Math and Type Conversion

The Number namespace handles arithmetic operations, rounding, and type conversion. Most of it is straightforward, but a few functions deserve specific mention.

Rounding — and why precision matters:

Number.Round(2.345, 2)           // 2.35 (standard rounding)
Number.RoundDown(2.987, 1)       // 2.9 (always floor)
Number.RoundUp(2.111, 1)         // 2.2 (always ceiling)
Number.Round(2.345, 2, RoundingMode.AwayFromZero)  // 2.35

Type conversion and safety:

Number.From("42.5")     // 42.5
Number.From(true)       // 1
Number.IsNaN(0/0)       // true  — "Not a Number" check
Number.IsEven(8)        // true
Number.Mod(17, 5)       // 2     — remainder after division
Number.Power(2, 10)     // 1024

Warning: Number.From will throw an error if the text can't be parsed as a number. If your data might have non-numeric values in a column you're converting, wrap it in a try/otherwise: try Number.From([Amount]) otherwise null.

---

How to Read a Function Signature You've Never Seen Before

When you look up an unfamiliar function — either in Microsoft's documentation or via #shared — you'll see a signature that looks something like this:

List.Select(list as list, condition as function) as list

Here's how to decode it:

• The part before the first ( is the full function name.
• Each parameter is listed with a name and a type, separated by as.
• The part after the closing ) and as is the return type — what the function gives back.
• A parameter type listed as optional type means you can leave it out.

Some signatures include the word any as a type, which means M won't enforce a specific type — it accepts whatever you give it. This is common in functions that deliberately work across multiple types.

---

Hands-On Exercise

Build this transformation from scratch using only the standard library functions covered in this lesson:

Scenario: You have a table called Orders with these columns: OrderID (text), CustomerName (text, formatted as "LAST, First"), OrderDate (text, formatted as "MM/dd/yyyy"), Amount (text, values like "$1,250.00").

Your goal: produce a clean table with OrderID, CleanName (formatted as "First Last"), OrderYear, OrderMonth, and Amount (as a proper number).

let
    // Step 1: Start with your source table
    Source = Orders,
    
    // Step 2: Parse OrderDate from text to a proper Date
    ParsedDate = Table.TransformColumns(Source, {
        {"OrderDate", each Date.FromText(_, [Format="MM/dd/yyyy"]), type date}
    }),
    
    // Step 3: Extract Year and Month
    AddYear = Table.AddColumn(ParsedDate, "OrderYear", 
        each Date.Year([OrderDate]), Int64.Type),
    AddMonth = Table.AddColumn(AddYear, "OrderMonth", 
        each Date.Month([OrderDate]), Int64.Type),
    
    // Step 4: Clean up CustomerName
    SplitNames = Table.AddColumn(AddMonth, "NameParts", 
        each Text.Split([CustomerName], ", ")),
    AddCleanName = Table.AddColumn(SplitNames, "CleanName",
        each Text.Proper([NameParts]{1}) & " " & Text.Proper([NameParts]{0})),
    
    // Step 5: Parse Amount — remove $ and comma, convert to number
    ParseAmount = Table.TransformColumns(AddCleanName, {
        {"Amount", each Number.From(Text.Replace(Text.Replace(_, "$", ""), ",", "")), type number}
    }),
    
    // Step 6: Keep only the columns we want
    FinalTable = Table.SelectColumns(ParseAmount, 
        {"OrderID", "CleanName", "OrderYear", "OrderMonth", "Amount"})
in
    FinalTable

Work through each step. Make sure you understand why each function was chosen, not just what it does.

---

Common Mistakes & Troubleshooting

Mistake 1: Using Date.From on text with a non-standard format Date.From("15/11/2024") will error or produce wrong results because M defaults to US date formats. Use Date.FromText with an explicit format string instead.

Mistake 2: Confusing List.Select with Table.SelectRows Both filter things, but List.Select works on a list and returns a list. Table.SelectRows works on a table and returns a table. The each condition inside Table.SelectRows receives a full record (one row), so you reference columns as [ColumnName]. In List.Select, the each receives a single value, referenced as _.

Mistake 3: Text.Replace is case-sensitive Text.Replace("Hello World", "hello", "Hi") returns "Hello World" unchanged. If you need case-insensitive replacement, use Text.Lower first or combine with Text.Contains with a Comparer.OrdinalIgnoreCase argument.

Mistake 4: Forgetting that Text.Split returns a list, not a table After splitting, you access elements with {0}, {1}, etc. (zero-indexed). Trying to access [0] (square brackets, which denote record fields) is a common typo that causes confusing errors.

Mistake 5: Chaining too many Table.AddColumn steps vs. using Table.TransformColumns If you're modifying an existing column's values, prefer Table.TransformColumns. Using Table.AddColumn plus Table.RemoveColumns to achieve the same thing is more steps and harder to read.

---

Summary & Next Steps

The M standard library stops being overwhelming the moment you understand it's organized by namespace, and each namespace corresponds to a data type. Text for strings, Date for dates, List for lists, Table for tables, Record for records, Number for numbers. When you need a function, your first question should be "what type am I working with?" and that question points you to the right neighborhood.

You also now have two practical tools for self-directed exploration: the #shared intrinsic keyword for live browsing, and the ability to read type signatures from the documentation. With those two tools, you can independently learn any function in the library.

To deepen your understanding, explore these topics next:

• Custom functions in M — once you've mastered the standard library, learn to build your own reusable functions with the same structure
• Error handling with try...otherwise — make your use of standard library functions robust against bad data
• The each keyword and function values — deepen your understanding of how higher-order functions like List.Transform and Table.SelectRows actually work
• The Value namespace — a meta-namespace for type-checking and introspection that unlocks more advanced patterns

The standard library is your toolbox. You now know where the tools are kept.