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:
Text, Date, List, Table, Number, and Record#shared intrinsic keyword to explore the library from inside Power QueryYou 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.
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.
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
TextOnlytable, 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 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 casingText.Trim / Text.TrimStart / Text.TrimEnd — remove whitespaceText.Replace — substitute one substring for anotherText.PadStart / Text.PadEnd — pad a string to a fixed lengthExtraction: Pull a piece of a string out.
Text.Start(text, count) — first N charactersText.End(text, count) — last N charactersText.Middle(text, offset, count) — substring by positionText.BetweenDelimiters(text, startDelim, endDelim) — extract what's between two markersTesting: Ask a question about a string and get true/false back.
Text.Contains(text, substring) — does it contain this?Text.StartsWith / Text.EndsWith — positional containmentText.Length(text) — how many characters?Splitting and Combining:
Text.Split(text, delimiter) — split into a listText.Combine(list, separator) — join a list into a stringText.SplitAny(text, separators) — split on any character in a setHere'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.
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.DayOfWeekuses a zero-based index starting on Sunday by default. So Monday = 1, Friday = 5. You can pass aDay.Mondayenum 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 DateDateTime.From(value) — converts text or a number to a DateTimeDate.FromText(text) — parses specifically from a text string using a known formatUse 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.
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 _ > 3is shorthand for(x) => x > 3. The underscore_is the implicit parameter name when usingeach. You'll see this pattern constantly inList.Select,List.Transform, and similar higher-order functions.
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.
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 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.Fromwill 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.
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:
( is the full function name.as.) and as is the return type — what the function gives back.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.
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.
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.
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:
try...otherwise — make your use of standard library functions robust against bad dataeach keyword and function values — deepen your understanding of how higher-order functions like List.Transform and Table.SelectRows actually workValue namespace — a meta-namespace for type-checking and introspection that unlocks more advanced patternsThe standard library is your toolbox. You now know where the tools are kept.
Learning Path: Advanced M Language