Power Query M
#datetimezone(year, month, day, hour, minute, second, offsetHours, offsetMinutes) constructs an exact Gregorian datetime with an explicit fixed offset. Local calendar fields must form a valid year 1–9999 datetime; offsets must resolve to whole minutes from −14:00 through +14:00. Z input is accepted and canonicalized to +00:00; visual Convert and Table.TransformColumnTypes text conversions require an ISO date-time with explicit Z or ±HH:MM. Naive text, date-only text, numbers, booleans, and null are not inferred as zoned values. Use DateTime.AddZone when assigning an offset to an existing naive datetime. This bounded fixed-offset subset does not apply local-machine or IANA daylight-saving rules.
DateTime.AddZone(datetime, offsetHours[, offsetMinutes]) keeps the wall-clock fields and assigns a fixed offset. DateTimeZone.SwitchZone(datetimezone, offsetHours[, offsetMinutes]) keeps the instant and changes its displayed offset; DateTimeZone.ToUtc keeps the instant at +00:00. DateTimeZone.RemoveZone returns the same local calendar and clock fields as a naive datetime. DateTimeZone.ZoneHours and DateTimeZone.ZoneMinutes return the signed offset components. DateTimeZone.ToRecord exposes Year, Month, Day, Hour, Minute, Second, ZoneHours, and ZoneMinutes; DateTimeZone.Type and type datetimezone identify the zoned value kind. Equality, ordering, and subtraction compare UTC instants; arithmetic by a duration preserves the offset and exact 100-nanosecond ticks. Canonical text uses up to seven fractional digits and an explicit ±HH:MM offset. Raw datetimezone values remain literal canonical ISO text with text (@) formatting in linked worksheets and XLSX, and exact strings in CSV, JSON, and Parquet exports; they are not numeric Excel dates. For a numeric UTC worksheet datetime, derive DateTimeZone.RemoveZone(DateTimeZone.ToUtc([Stamp])); numeric Excel serial precision is separate from the exact zone text. Local fields are bounded to years 1–9999, and a UTC conversion that leaves that range is a typed error.
The optional format argument to Duration.ToText(duration[, format]) must be null; Duration.From has no culture argument. Applying a duration to a date preserves the date for whole-day offsets and promotes fractional-day offsets to a timezone-naive datetime. Date and datetime offsets use exact 100-nanosecond ticks; results remain bounded to years 1–9999.
#time(hour, minute, second) creates an immutable local time. Hour and minute must be finite whole numbers in 0–24 and 0–59; second must be finite and in [0, 60), with fractional digits beyond seven truncated. Hour 24 is valid only when minute and second are exactly zero. Time.From explicitly converts a time, timezone-naive datetime, finite day fraction in [0, 1), or clock text under en-US, en-GB, fr-FR, or de-DE. Strict ISO clock text is HH:MM:SS[.fraction] with up to seven fractional digits; the query value retains exact 100-nanosecond ticks. 24:00:00 is a distinct end-of-day value, not midnight. Time.Hour, Time.Minute, and Time.Second read typed time values or timezone-naive datetimes; null remains null. Number.From(time) returns a day fraction and Text.From(time) returns clock text. Worksheet storage uses a floating numeric day fraction, so arbitrary 100-nanosecond ticks may be approximated. The endpoint is exactly serial 1; ordinary clock formats display it wrapped to midnight, while elapsed [h]:mm:ss displays 24:00:00. Timezone-aware values, date-only values, and unsupported cultures are rejected. This is a bounded local subset without timezone support or full Mashup parity.
#duration(days, hours, minutes, seconds) sums signed components before normalization; days, hours, and minutes must be whole finite numbers, while seconds is finite and truncated toward zero to 100-nanosecond ticks. For example, #duration(0, 24, 0, 0) is 1.00:00:00; mixed signs are summed, then normalized. Duration values are immutable signed 64-bit tick counts. Duration.From accepts a duration, bounded duration text, or finite numeric day count; Duration.FromText parses bounded signed day/clock forms, and Duration.ToText emits canonical [-]d.hh:mm:ss[.fraction] text with up to seven fractional digits. Duration.Days, Duration.Hours, Duration.Minutes, and Duration.Seconds return signed components; Duration.TotalDays, Duration.TotalHours, Duration.TotalMinutes, and Duration.TotalSeconds return signed totals. Number.From(duration) returns day fractions and Text.From(duration) returns canonical text. Duration arithmetic uses exact ticks for bounded duration addition, subtraction, scaling, and division; adding/subtracting a duration to/from time wraps at 24 hours. Date/datetime offsets have bounded precision and range rules, not full host parity. Worksheet storage uses floating day fractions and may approximate 100-nanosecond ticks. Negative duration values are preserved by the model, but native display in Excel's 1900 date system has compatibility limits; elapsed [h]:mm:ss is the intended display. Timezone arithmetic and full Mashup parity are unsupported.
Text.Trim(text[, characters]), Text.TrimStart, and Text.TrimEnd remove selected edge characters; Text.Clean removes control characters; Text.Upper and Text.Lower change case. The visual Transform text step is in-place, preserves null and existing errors, and reports nontext values as unresolved errors. Text cleanup and case behavior here are bounded local rules, not full Power Query parity.
Number.Round(number[, digits[, roundingMode]]) rounds to the nearest value; omitted or null digits means zero, and the default midpoint mode is RoundingMode.ToEven. The mode constants are RoundingMode.Up (0), RoundingMode.Down (1), RoundingMode.AwayFromZero (2), RoundingMode.TowardZero (3), and RoundingMode.ToEven (4). Number.RoundUp, Number.RoundDown, Number.RoundTowardZero, and Number.RoundAwayFromZero accept an optional digits argument; Number.Abs returns absolute value. The visual Transform numbers step preserves nulls and existing errors, while nonnumeric selected values remain unresolved errors. Its decimal-facing policy is bounded and is not full Power Query or binary floating-point parity.
Text.Start, Text.End, Text.Middle, Text.BeforeDelimiter, Text.AfterDelimiter, and Text.BetweenDelimiters extract text by character count or literal delimiters. The visual Extract text step indexes Unicode code points (not grapheme clusters), supports occurrences from either edge, and searches a Between end delimiter only in the suffix after the selected start. Its missing-match and nontext behavior is a bounded local rule, not full Power Query parity.
List.IsEmpty(list) tests whether a list has no items. List.RemoveMatchingItems(list1, list2[, equationCriteria]) removes matching items; the default or explicit null comparer uses typed scalar equality, while non-null comparers and structured compared values are unsupported here. For rows without forced errors, an equivalent expression for removing rows whose cells are all null or exact empty text is Table.SelectRows(Input, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))). Actual errors forced while reading fields follow existing M error propagation, so equivalence is not promised on error rows.
List.Combine(lists) flattens one list level in order; deeper nested members remain intact. Table.ExpandListColumn(table, column) emits one row per list member and duplicates other fields; an empty list removes its row, while null locally emits one null row. Table.ExpandRecordColumn(table, column, fields[, newNames]) replaces a record column with selected fields, defaulting output names to field names. Null records yield null fields; missing selected fields are typed errors. New names must be unique and cannot collide with existing columns; unselected fields remain lazy. For a nested table column, Table.ExpandListColumn emits its rows as records; these records can then be projected with Table.ExpandRecordColumn. These functions have aliases/callbacks. Lists, records, and nested tables are bounded private cells and cannot be returned unexpanded as final query output. Unsupported values or operations fail rather than silently dropping data. Shared work, row, cell, byte, and retained-graph budgets apply. This is a bounded local subset, not full Power Query parity.
Text.Combine(list[, separator]) joins text values; an omitted or null separator means empty text. Null members are ignored, empty text members remain, and nontext members fail with m_type_error rather than being stringified. Table.TransformColumns can reduce private lists/records to scalars or consistently transform their private kind. An explicit scalar output type clears the old private kind, including empty/all-null results; without a hint, ambiguous null/empty results retain the old kind. Mixed structured kinds and conflicting hints fail. Shared list, work, and retained-graph budgets apply. These are bounded local rules, not full Power Query parity.
Text.Start(text, count) and Text.End(text, count) return up to count code points; zero returns empty text. Text.Middle(text, start[, count]) and Text.Range(text, offset[, count]) take text from a zero-based position. All six functions return nullable text. Positions count Unicode code points, as Text.Length does, not grapheme clusters. Counts, starts, and offsets must be finite, nonnegative whole numbers; booleans and fractions are invalid. For Middle and Range, an omitted or null count means the rest of the text. Middle clamps an overlong count and returns empty when its start is past the end. Range requires the requested range to fit; an offset past the end or a requested range past the end raises m_invalid_text_range. An offset exactly at the end is valid with zero, omitted, or null count and returns empty. Text.PadStart(text, count[, character]) and Text.PadEnd(text, count[, character]) add characters to reach a length and never truncate text; the omitted or null padding character is a space, and an explicit character must be one Unicode code point. A null source returns null after its options are validated. Existing cell errors pass through. try expression returns a record with HasError and either Value or Error; try expression otherwise fallback and try expression catch () => fallback provide fallback values, while catch (e) => ... can inspect fields such as e[Reason]. Author an error with error "message", error [Reason="BusinessRule", Message="...", Detail=[...], ErrorCode="B42"], or error Error.Record(reason[, message[, detail[, parameters[, errorCode]]]]). A successful try does not force deferred list or record members; put the member read inside try when it should be caught. Resource, security, and unsupported-feature errors cannot be recovered. Inputs and results share a 65,536-byte UTF-8 limit and work limits. Values are not silently converted to text; use Text.From explicitly. Examples: preserve leading zeroes with Text.PadStart([ID], 6, "0"), or use Start/End for prefixes and suffixes and Middle/Range for category extraction. These are bounded local rules, not full Power Query or Excel parity.
Text.BeforeDelimiter(text, delimiter[, index]) and Text.AfterDelimiter(text, delimiter[, index]) return nullable text. Text.BetweenDelimiters(text, startDelimiter, endDelimiter[, startIndex[, endIndex]]) also returns nullable text; indices default to zero and accept a nonnegative whole-number occurrence or {index, RelativePosition.FromStart|RelativePosition.FromEnd}. BetweenDelimiters counts its end occurrence only in the suffix after the selected start. Text.Split(text, delimiter) uses a full delimiter; Text.SplitAny(text, delimiters) uses each delimiter character. Both retain empty fields. Local policy: missing/out-of-range occurrences raise m_delimiter_not_found; empty delimiters raise m_invalid_delimiter, except empty SplitAny delimiters return the original value as one field. Unicode indexing follows code points; shared item, work, and retained-graph budgets apply. These are bounded local rules, not full Power Query parity.
List.Accumulate(list, seed, (state, item) => expression) folds items in order and returns the seed for an empty list. It supports scalar, list, record, table, and function states. List.Generate(() => initial, each condition, each next[, each selector]) lazily emits states while the condition is true; a null or omitted selector returns each state. Counting evaluates generation but does not force selector outputs. This bounded runtime requires logical conditions and enforces item, work, and retained-graph limits. List & is lazy concatenation; record & keeps left field order, lets right fields win, and leaves merged fields lazy. Text & remains concatenation. Deeply deferred record/function states can hit evaluation or retained-graph limits. Example running totals: List.Accumulate(Input[Amount], {}, (state, item) => state & {List.Last(state, 0) + item}). Example record state: List.Accumulate(Input[Amount], [Total = 0, Count = 0], (state, item) => [Total = state[Total] + item, Count = state[Count] + 1]). List.Transform maps list items; List.Select keeps items whose predicate is true. List.Reverse lazily reverses member order; List.Sort stably sorts compatible values, with optional order, unary key selector, selector/order pair, or binary comparator. Navigation also includes List.First/List.Last with optional defaults, List.FirstN/List.LastN and List.Skip with counts or unary predicates, List.Range, and List.RemoveNulls. Counts and offsets are nonnegative whole numbers; Skip defaults to one, a null Range count keeps the remainder, and LastN requires its second argument. Predicates stop at false or null, while LastN preserves source order. Example: let Clean = List.RemoveNulls(Input[Amount]), Head = List.FirstN(Clean, Parameters[Count]), Tail = List.LastN(Clean, 2), Window = List.Range(List.Skip(Clean, 1), 0, 2) in Table.FromColumns({Head, Tail, Window}, {"Head", "Tail", "Window"}).
Power Query M uses the bounded server interpreter with Input and Parameters. Supported functions include Table.SelectRows, Table.SelectRowsWithErrors, Table.RemoveRowsWithErrors, Table.AddColumn, Table.AddIndexColumn, Table.TransformColumnTypes, Table.TransformColumns, Table.ReplaceValue, Table.ReplaceErrorValues, Table.Unpivot, Table.UnpivotOtherColumns, Table.Pivot, Table.SelectColumns, Table.RemoveColumns, Table.RenameColumns, Table.DuplicateColumn, Table.Sort, Table.FirstN, Table.LastN, Table.RemoveFirstN, Table.RemoveLastN, Table.ReverseRows, Table.AlternateRows, Table.FillDown, Table.FillUp, Table.Distinct, Table.Group, Table.Combine, Table.RowCount, Table.ColumnNames, Table.HasColumns, Table.Column, Table.ToColumns, Table.ToRows, List.Generate, List.Sort, List.Reverse, List.Distinct, List.Sum, List.Average, List.Min, List.Max, List.Count, and List.NonNullCount.
Amounts{0} returns the first list item; Input{0} returns the first table row as a record. A key selector such as Input{[ID="row-001"]} must match exactly one row; duplicate matches are errors. Follow a selected row with [Field], as in Input{0}[Amount]. A trailing ? makes only a missing selection return null, as in Amounts{99}? or Input{[ID="absent"]}?; without it, a missing item or row is an error. Optional access does not hide invalid indices, duplicate matches, field-access errors, or source errors. Indices must be finite, nonnegative whole numbers; booleans and fractions are invalid. Key fields must be scalar values and are compared without text coercion. Row access is lazy, so unselected fields are not forced. These are bounded local rules, not full Power Query parity.
Record field access uses brackets: row[Amount] reads one field, and row[Amount]? returns null only when that field is absent. Record projection uses an ordered field list, for example row[[ID],[Note]]; a trailing ? permits missing projected fields, as in row[[ID],[Region]]?. Table projection uses the same ordered form: Input[[Note],[ID],[Amount]]? keeps that output order and locally fills an absent field with null. The implicit row form each [Region]? reads the current row without forcing unrelated fields. Record projections leave unselected fields lazy; table projections read selected columns after source admission. Optional access does not swallow a null target, a source error, or an error while reading a selected field. A selected field is callable only when it holds an admitted builtin or lambda; host functions and general dynamic evaluation remain unavailable. These are bounded local rules, not full Power Query parity.
For schema drift and optional columns, Table.SelectColumns and Table.RemoveColumns accept one column name or an ordered list of names. Table.RenameColumns accepts one old/new name pair or an ordered list of pairs. Their optional third missingField argument defaults to MissingField.Error (also 0): an absent requested name still fails unless you explicitly choose MissingField.Ignore (1) or MissingField.UseNull (2). SelectColumns with UseNull inserts a null-filled field at its requested position; Ignore skips absent names. RemoveColumns with Ignore or UseNull skips absent names and never creates a result column. RenameColumns processes pairs left to right: Ignore skips a pair whose source is absent before checking its target, while UseNull appends a null-filled target for an absent source. Renaming onto a real existing column is still a typed collision error. Existing column types and nested values follow retained or renamed fields; UseNull does not infer a useful type for an all-null new column, so explicitly use Table.TransformColumnTypes (for example, type nullable text) when a downstream query needs a typed blank field. These bounded local rules retain the existing query, row, cell, byte, work, and evaluation guards; they are not full Power Query or Mashup compatibility.
Record navigation helpers operate on admitted interpreter records. Field names are exact, case-sensitive text; they can be supplied dynamically, as in Record.Field(_, Parameters[Field]). Record.Field requires the named field and errors when it is absent. Record.FieldOrDefault(_, "Region", "unknown") uses its default only for an absent field; an existing null, false, zero, or empty text value is preserved. Its default is an ordinary eager function argument, not lazy like ?? and not an error handler. Record.HasFields(_, {"ID", "Amount"}), Record.FieldCount(_), and Record.FieldNames(_) inspect names or presence without forcing field values; for example, Text.Combine(Record.FieldNames(_), ","). Record.FieldValues([First=0, Later=error "unused"]){0} and Record.ToList([First=false, Later=error "unused"]){0} preserve record order and force only the selected member. Existing query, work, and retained-graph bounds apply. These functions inspect interpreter records only; host-object inspection and dynamic evaluation remain unavailable. This is a bounded local subset, not full M or Mashup compatibility.
The Power Query M null-coalescing operator ?? returns its left operand unless it is null, then evaluates the right operand: [Amount] ?? 0, [Region]? ?? "unknown", or Input{99}? ?? [ID="missing"]. Fallbacks can be chained, as in [Primary] ?? [Backup] ?? 0. Zero, false, and empty text are values, not missing data. The right operand is lazy and is evaluated only when the left operand is null. This is null-only fallback, not error handling; use try expression otherwise fallback for recoverable errors. Coalescing has lower precedence than logical or, so use parentheses to clarify compound conditions. Both operands are still parsed and admitted, so a skipped fallback cannot hide unsupported or unsafe code. Existing query bounds and budgets still apply; this is a bounded local subset, not full Power Query or native Mashup parity.
The M type operators is and as test or assert types; they do not convert values. For example, [Amount] is number returns a logical value, false for a nonmatching admitted kind, while [Amount] as nullable number preserves a compatible original value or errors. Numeric text stays text, and booleans are not numbers. Use try ([Amount] as number) otherwise 0 to recover from a catchable cast error; ?? handles null only, not errors. Parentheses matter when casting before arithmetic: ( [Amount] as number ) + 1. Supported primitive type targets are any, anynonnull, none, null, logical, number, text, binary, date, datetime, datetimezone, duration, time, list, record, table, function, and type. These primitive type keywords are legal targets; arbitrary dynamic, custom, or alias target expressions and unsupported value constructors remain unavailable. A nullable modifier applies to primitive targets and permits null, as in [Amount] as nullable number and ([Region]? as nullable text) ?? "unknown". List, record, table, and function values are classified shallowly without forcing members. Existing query bounds and budgets apply; this is a bounded local subset, not full M or Mashup compatibility.
Table.ColumnNames(table) returns exact column names in source order without reading rows. Table.HasColumns(table, columns) checks exact, case-sensitive names; an empty requested list is true. Table.Column(table, column) returns values in row order. Table.ToColumns(table) returns lists in column order, and Table.ToRows(table) returns rows as ordered lists. Table.FromColumns(columns[, names]) and Table.FromRows(rows[, names]) rebuild a table with those names and order. These bounded local operations preserve nulls, booleans, and literal text without coercion; zero-column Table.ToRows retains one empty row per source row. They are a local subset, not full Power Query parity.
Table.FirstN(table, countOrCondition) and Table.LastN(table, countOrCondition) require a count or supported unary row condition; conditions stop at the first false or null row. Table.RemoveFirstN(table[, countOrCondition]) and Table.RemoveLastN(table[, countOrCondition]) remove contiguous prefixes or suffixes and default to one row when omitted; a supported unary condition scans from that edge through the first false or null row. Retained rows stay in source order. These are bounded local rules, not full Power Query parity.
Table.ReverseRows(table) reverses the full input table. Table.AlternateRows(table, offset, skip, take) keeps the initial offset rows, then repeatedly skips skip rows and keeps take rows; kept rows retain original order and the final cycle is clamped to the input. Offset, skip, and take are nonnegative safe whole numbers; skip and take cannot both be zero. These are bounded local rules, not full Power Query parity.
Table.SelectRowsWithErrors(Input, {"Amount"}) keeps rows with typed errors in the listed columns; Table.RemoveRowsWithErrors(Input) removes rows with typed errors across all columns. Omitted or null columns means all columns; an explicit list checks only those fields. Literal text that resembles an error is not a typed error. Unresolved typed errors still prevent Apply until repaired with Table.ReplaceErrorValues.
Table.Combine({Sources[Jan], Sources[Feb]}) consolidates up to eight tables in authored row order, keeps first-seen column order, and pads missing fields with null. An optional unique column-name list or scalar-field table type projects ordered fields; type hints do not cast values. Consistent nested table columns remain available for expansion.
Table.SplitColumn(Input, "Details", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv, CsvStyle.QuoteAfterDelimiter), {"Name", "Amount"}, null, ExtraValues.Ignore) replaces a text column with ordered fields. CSV quoting supports quoted delimiters, doubled quotes and embedded newlines; QuoteStyle.None and CsvStyle.QuoteAlways are also supported. Missing fields use the default; overflow can be ignored or reported with ExtraValues.Error. ExtraValues.List is unsupported because list-valued table cells are not admitted. Local policies: omitted/null quote options use None and QuoteAfterDelimiter; omitted/null output names infer the maximum successful row width, minimum one. Numeric output counts must be positive; zero is locally unsupported. A bounded unary M lambda returning a list can also supply fields.
Table.TransformColumns applies a callback to selected columns and can include an output type, a default callback for other columns, and a MissingField policy. Table.ReplaceValue supports exact Replacer.ReplaceValue, literal Replacer.ReplaceText, and row-aware custom replacers; Table.ReplaceErrorValues replaces typed cell errors only. Callback helpers include Number.FromText, Text.From, Text.Length, and Text.Repeat. For example: Table.TransformColumns(Input, {{"Amount", each Number.FromText(Text.Trim(_), "en-US"), type number}}, null, MissingField.Ignore). Supported callbacks and scalar conversions are bounded local behavior, not full Power Query parity.
Table.Unpivot follows its selected-column list; Table.UnpivotOtherColumns keeps the listed columns as keys and unpivots the remaining columns. Both skip null values. Table.Pivot accepts a list of text headings and an optional null or unary aggregation function. It keeps key columns in source order, groups in first-seen order, emits headings in requested order, and fills missing headings with null. Without an aggregation function, duplicate key/heading values become typed cell errors. List.Distinct takes a list and optional unary selector, keeping first-seen typed scalar values. Admitted aggregates are List.Sum, List.Average, List.Median, List.Min, List.Max, List.Count, and List.NonNullCount. For example: let Long = Table.UnpivotOtherColumns(Input, {"Customer Ω", "Region"}, "Month", "Amount"), Wide = Table.Pivot(Long, List.Distinct(Long[Month]), "Month", "Amount", List.Sum) in Wide. These reshape and list rules are bounded local policies, not full Power Query parity.
For example: Table.TransformColumnTypes(Input, {{"Amount", type number}, {"When", type nullable datetime}}, "fr-FR"). A single pair is also accepted; a list can contain up to 256 unique column/type pairs, and an empty list leaves the input unchanged. Supported types are number, Int64.Type, text, logical, date, and datetime, optionally nullable; Number.Type, Text.Type, Logical.Type, Date.Type, and DateTime.Type aliases are also accepted.
The optional third argument is a culture or [Culture="fr-FR", MissingField=MissingField.UseNull]. Cultures are en-US (default), en-GB, fr-FR, and de-DE. MissingField.Error is the default; MissingField.Ignore skips absent columns and MissingField.UseNull adds them with null values. Numeric text uses strict locale separators; dates accept ISO or locale numeric forms, and timezone-bearing datetimes are rejected. Date serials use the OLE base date, not the worksheet date epoch. Parsing and formatting are bounded local policies, not full Power Query or Excel parity.
#date(year, month, day) creates a Gregorian date from year 1 through 9999; it has no fictitious Excel 1900-02-29. Date.From(value[, culture]) explicitly converts a date, naive datetime, number using the OLE base 1899-12-30, or date text using en-US by default (also en-GB, fr-FR, and de-DE). It returns null for null; unsupported cultures, values, and timezone-aware datetimes fail closed. Date extractors accept typed dates, naive datetimes, and explicit fixed-offset datetimezones; zone extraction uses local calendar fields, so convert to UTC first when UTC parts are intended. Extractors do not coerce untyped text or numbers; use Date.From for supported explicit conversions. Date.Year, Date.Month, Date.Day, Date.DayOfYear, Date.DaysInMonth, and Date.QuarterOfYear return numeric parts; Date.DayOfWeek(date[, firstDay]) returns Sunday 0 through Saturday 6 when Day.Sunday is used as the first day. These are bounded local calendar rules, not full Power Query parity.
#datetime(year, month, day, hour, minute, second) creates a timezone-naive Gregorian datetime from year 1 through 9999. Year, month, day, hour, and minute must be finite numeric whole numbers (year 1–9999, month 1–12, day valid for that month, hour 0–23, minute 0–59); seconds must be a finite number with 0 ≤ second < 60. Booleans, text, and null are not coerced. Fractional seconds are truncated to seven decimal places (100-nanosecond ticks), so 59.9999999 remains 59.9999999. The value preserves exact bounded 100-nanosecond ticks; this does not add timezone behavior or full Power Query parity. Arguments must form a valid calendar date and time; invalid values remain typed errors. #date and #datetime are safe first-class aliases: let MakeDate = #date in MakeDate(2024, 2, 29) and let Make = #datetime in Table.AddColumn(Input, "Made", each Make(2024, 2, 29, 12, 30, 0), type datetime). For example, Date.EndOfMonth(#datetime(2024, 2, 29, 23, 59, 59.9999999)) returns a naive datetime ending at 23:59:59.9999999, the last 100-nanosecond tick of that day. These constructors work in ordinary saved M queries under existing work, size, and error guards; other constructors such as #table are not enabled as aliases.
Date.AddDays(date, days), Date.AddWeeks(date, weeks), Date.AddMonths(date, months), Date.AddQuarters(date, quarters), and Date.AddYears(date, years) each require exactly two arguments and accept a nullable typed date, timezone-naive datetime, or explicit fixed-offset datetimezone plus a signed whole finite offset (an integer or integral number). Day and week offsets count local calendar days and weeks; month, quarter, and year shifts clamp to the target month’s last day while preserving clock fields, the explicit zone offset, and exact 100-nanosecond ticks. Results use real Gregorian years 1–9999; an out-of-range result is a typed error. For example, Date.AddMonths(#date(2024,1,31), 1) is 2024-02-29 and Date.AddQuarters(#date(2024,1,31), 1) is 2024-04-30. Convert text or numeric inputs explicitly with Date.From; create typed datetimes with Table.TransformColumnTypes and type nullable datetime. Null source values return null only after the offset is validated. Null, boolean, fractional, or non-finite offsets produce typed errors. For datetimezones, results use the source local calendar and fixed offset, without machine/IANA daylight-saving rules. These are bounded local whole-offset rules, not complete Power Query or fractional-offset semantics.
Date.StartOfDay(date), Date.StartOfWeek(date, firstDayOfWeek), Date.StartOfMonth(date), Date.StartOfQuarter(date), and Date.StartOfYear(date) start the containing calendar period. A date stays a date; naive datetimes stay datetimes, and datetimezones use their local date while retaining the fixed offset. Day, week, quarter, and year starts reset clock fields and 100-nanosecond ticks to midnight. The week option is optional; omitted or null means Sunday, and Day.Sunday is 0 through Day.Saturday at 6 (for example, Day.Monday is 1). Null source values return null after the week option is validated. Date.StartOfMonth follows its date/datetime-only contract and refuses datetimezone; use DateTimeZone.RemoveZone explicitly for month starts. Other listed starts accept fixed-offset datetimezones and use local calendar fields. Inputs must be typed dates, datetimes, or (where supported) datetimezones: convert supported text or numbers with Date.From, or type datetime columns with Table.TransformColumnTypes and type nullable datetime. Supported years are 1–9999; a week start before year 1 produces a typed error. These are bounded local rules, not full Power Query or timezone semantics; no machine or IANA daylight-saving rules are applied.
Date.EndOfDay(date), Date.EndOfWeek(date[, firstDayOfWeek]), Date.EndOfMonth(date), Date.EndOfQuarter(date), and Date.EndOfYear(date) end the containing Gregorian period for typed dates, naive datetimes, or explicit fixed-offset datetimezones. Zones use their local date, retain the fixed offset, and end at the local period’s 23:59:59.9999999, the last 100-nanosecond tick; use UTC conversion first for UTC periods. Dates stay dates; datetimes end at 23:59:59.9999999. The week option is optional; omitted or null means Sunday, with Day.Sunday as 0 through Day.Saturday as 6. Null source values return null after any explicit weekday is validated; existing query error/work guards still apply. Convert text or numeric inputs explicitly with Date.From; invalid values and out-of-range results remain typed errors. No machine or IANA daylight-saving rules are applied. These are bounded local rules, not full Power Query parity.
Table.AddIndexColumn defaults to start 0 and increment 1; null uses those defaults, and Number.Type or Int64.Type may be supplied as the column type. Numeric conversion and rounding are local bounded policies, not full Power Query or Excel parity.
For Table.Distinct, omitted/null columns select all columns and a single string or string list selects columns; deterministic first-row retention is local policy, not a general Power Query guarantee.
Table.Group(Input, keys, aggregations) requires scalar keys; private list/record keys fail with m_type_error, and nested-table keys are unsupported. Nonkey list, record, and nested-table columns remain available intact inside each subgroup. Aggregates may return lists, records, or tables; use matching List.Type or Record.Type hints where needed. Reduce or expand private results before final query output. GroupKind.Global uses first-seen groups and GroupKind.Local groups consecutive runs as application ordering policy; Power Query does not guarantee output row order. A null comparer keeps default behavior; non-null comparers are unsupported. Shared work, row, cell, byte, and retained-graph budgets apply.
List.Count counts every item, including nulls; List.NonNullCount counts non-null items; and Table.RowCount counts physical rows. These structure/count operations need not inspect or propagate typed-error payloads in unreferenced values, unlike value aggregates such as Sum or Min.
List.Sum, List.Average, List.Median, List.Min, and List.Max accept bounded materialized scalar lists. Sum, Average, and Median ignore nulls and accept homogeneous numeric or duration values; mixed numeric/duration inputs are rejected. Duration averages truncate toward zero at tick precision; even-count duration medians use ties-to-even. Min and Max compare one compatible scalar family and include null by default, where null sorts below non-null values. Standard deviation remains numeric-only and rejects nulls. Typed cell errors remain errors, rather than being converted to text or skipped. List scans are capped at 250,000 items, each UTF-8 text value/output at 64 KiB, and total list text at 16 MiB. Numeric aggregates use exact binary-ratio accumulation with one final float conversion as a local stability policy, not general M double-rounding equivalence; a nonzero result that underflows to zero is rejected. Unsupported values, mixed comparison families, timezone-aware datetimes, and Decimal-precision aggregation are not admitted. These are deterministic local subset rules, not full Power Query parity.