You've just inherited a Power Query solution from a colleague who's moved on. The M code is hundreds of lines long, the queries feed six downstream reports, and one of them is throwing a cryptic error about a column type mismatch — somewhere. You dig into the Applied Steps, squinting at transformation after transformation, trying to figure out where the pipeline assumes a column is text when it's actually a number. This kind of debugging session can eat half a day. But here's the thing: it didn't have to be this way.
M Language metadata is one of the most underused features in Power Query. It's not glamorous — you won't find it in a "Top 10 Power Query Tips" video — but it's one of the primary tools that separates a brittle, maintenance-heavy pipeline from a robust, self-documenting one. When you attach metadata to values, columns, and types, you give your queries a memory. They can carry contextual information — descriptions, validation rules, display hints, lineage tags — through every transformation step without cluttering your business logic. And when something goes wrong, that metadata is there to tell you why.
By the end of this lesson, you'll be able to work with M metadata as a first-class part of your data engineering toolkit. You won't just know the syntax; you'll understand the underlying model well enough to design metadata-driven pipelines that are easier to debug, document, and hand off.
What you'll learn:
Value.ReplaceMetadata, Value.Metadata, and type annotationsThis lesson assumes you're comfortable with:
let...in blocks and custom functions)type text, type number, table type definitionsTable.TransformColumnTypes and Table.Schema functionsIf records or custom functions feel shaky, revisit those fundamentals first. Everything in this lesson builds on them.
Before writing a single line of code, you need a clear mental model. M metadata is best understood as a parallel record that travels alongside any value without being part of that value.
Every M value — a number, a text string, a table, a column — can have an associated metadata record. That record is invisible to most operations. When you add two numbers together, the result doesn't inherit either operand's metadata. When you filter a table, the row-level metadata from the removed rows doesn't appear in an error. The metadata is attached but inert — it only does something when you explicitly read it or when specific M functions are designed to interpret it.
This separation is intentional and powerful. It means you can annotate values with rich contextual information without changing their behavior in any computation. A column named Revenue with metadata [Description = "Net revenue after returns, in USD", Source = "ERP.SalesHeader"] behaves exactly like a plain column in every transformation. But when your schema validation function runs, it can read that metadata and confirm the column is what you expect it to be.
Internally, M represents a typed value as a pairing of a value and a type, and the type itself can carry metadata. The syntax value meta record is how you express this. 42 meta [Unit = "USD"] is the number 42 with a metadata record containing one field.
// Creating a simple annotated value
let
RawRevenue = 1250000.00,
AnnotatedRevenue = RawRevenue meta [
Description = "Net revenue after returns",
Currency = "USD",
Source = "ERP.SalesHeader",
LastVerified = #date(2024, 6, 1)
]
in
AnnotatedRevenue
The result of this query evaluates to 1250000.00. The metadata is there, but Power Query's output grid shows you only the value. To see the metadata, you need to ask for it explicitly.
The companion to meta is Value.Metadata. It takes any M value and returns its metadata record. If there's no metadata, you get an empty record [].
let
AnnotatedRevenue = 1250000.00 meta [
Description = "Net revenue after returns",
Currency = "USD",
Source = "ERP.SalesHeader"
],
// Read all metadata
AllMeta = Value.Metadata(AnnotatedRevenue),
// Read a specific field
SourceSystem = Value.Metadata(AnnotatedRevenue)[Source],
// Safe read with fallback (metadata field may not exist)
Unit = Record.FieldOrDefault(Value.Metadata(AnnotatedRevenue), "Currency", "Unknown")
in
Unit
The Record.FieldOrDefault pattern is important in practice. When you're reading metadata you didn't personally attach — perhaps from an external query or a function return — you can't guarantee every field exists. Defensive reading with FieldOrDefault keeps your pipeline from breaking on missing annotations.
Tip:
Value.Metadataon a value with no metadata returns[], notnull. Test for empty metadata withValue.Metadata(x) = []rather thanValue.Metadata(x) = null.
The meta keyword creates a new value with a specific metadata record, replacing whatever was there before. If you want to add fields to existing metadata rather than overwrite it, you need to merge the records manually.
let
// Start with an annotated value
Step1 = 1250000.00 meta [Source = "ERP.SalesHeader"],
// WRONG: This overwrites Source with only the new field
WrongUpdate = Step1 meta [Currency = "USD"],
// RIGHT: Merge the existing metadata with new fields
ExistingMeta = Value.Metadata(Step1),
NewMeta = Record.Combine({ExistingMeta, [Currency = "USD", Verified = true]}),
CorrectUpdate = Value.ReplaceMetadata(Step1, NewMeta)
in
Value.Metadata(CorrectUpdate)
// Returns [Source = "ERP.SalesHeader", Currency = "USD", Verified = true]
Value.ReplaceMetadata(value, metadataRecord) is the functional alternative to the meta keyword — they're equivalent in effect, but Value.ReplaceMetadata is easier to compose in multi-step logic since it accepts computed records.
Warning: Using
metain a chain without merging is one of the most common metadata bugs. If you writesomeValue meta [NewField = x]andsomeValuealready had metadata, you've silently discarded all the original annotations. Always merge unless you deliberately want to replace everything.
Attaching metadata to scalar values is useful, but the real power in a data pipeline comes from annotating tables and their columns. This is where you can build genuinely self-describing datasets.
let
Source = Csv.Document(
File.Contents("C:\Data\sales_2024.csv"),
[Delimiter=",", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
TypedTable = Table.TransformColumnTypes(PromotedHeaders, {
{"OrderID", Int64.Type},
{"OrderDate", type date},
{"CustomerID", Int64.Type},
{"Region", type text},
{"Product", type text},
{"Quantity", Int64.Type},
{"UnitPrice", type number},
{"Revenue", type number}
}),
// Attach pipeline metadata to the table itself
AnnotatedTable = TypedTable meta [
QueryName = "Sales_2024",
SourceFile = "sales_2024.csv",
LoadedAt = DateTime.LocalNow(),
RowCountExpected = 50000,
Owner = "Analytics Engineering",
SchemaVersion = "2.1"
]
in
AnnotatedTable
When this table flows into downstream queries, they can read Value.Metadata(Sales_2024) and know exactly where it came from, who owns it, and what schema version it's supposed to be.
Column-level metadata requires working with M's type system more directly. Each column in a table has a type, and that type can carry metadata. The mechanism is type text meta [...] or building custom type records.
let
// Define column types with embedded metadata
RevenueType = type number meta [
Description = "Net revenue after returns and discounts",
Currency = "USD",
Nullable = false,
BusinessOwner = "Finance"
],
RegionType = type text meta [
Description = "Sales region code",
AllowedValues = {"AMER", "EMEA", "APAC"},
Nullable = false
],
// Build a full table type with annotated columns
AnnotatedTableType = type table [
OrderID = (type number meta [Description = "Unique order identifier"]),
OrderDate = (type date meta [Description = "Date the order was placed"]),
Region = RegionType,
Revenue = RevenueType
],
// Apply this type to an existing table
Source = #table(
{"OrderID", "OrderDate", "Region", "Revenue"},
{{1001, #date(2024,1,15), "AMER", 4500.00},
{1002, #date(2024,1,16), "EMEA", 7200.50},
{1003, #date(2024,1,17), "APAC", 3100.75}}
),
TypedTable = Value.ReplaceType(Source, AnnotatedTableType)
in
TypedTable
Value.ReplaceType is distinct from Value.ReplaceMetadata. It replaces the type of a value (which includes any metadata stored in that type) while keeping the underlying data intact. This is how you stamp a rich type definition onto an existing table.
Once you've annotated columns, you need to be able to read those annotations systematically. The key function is Table.Schema, which returns a table describing every column — but by default it doesn't surface your custom metadata. You have to extract it manually.
let
// Assume TypedTable from the previous example
// Table.Schema gives us columns and their base types
Schema = Table.Schema(TypedTable),
// To get custom metadata from a specific column's type,
// we need to access the type annotation directly
// Get the type of the Revenue column
RevenueColumnType = Type.TableColumn(Value.Type(TypedTable), "Revenue"),
// Read its metadata
RevenueMeta = Value.Metadata(RevenueColumnType),
// Result: [Description = "Net revenue after returns...", Currency = "USD", ...]
RevenueDescription = RevenueMeta[Description]
in
RevenueDescription
Type.TableColumn(tableType, columnName) retrieves the type associated with a specific column within a table type. Combined with Value.Metadata, this lets you drill into any column's annotations.
For production use, you'll typically want to extract metadata for all columns at once:
let
TableType = Value.Type(TypedTable),
ColumnNames = Table.ColumnNames(TypedTable),
// Build a record of {ColumnName -> MetadataRecord} for all columns
MetadataByColumn = List.Transform(
ColumnNames,
(colName) => {
colName,
Value.Metadata(Type.TableColumn(TableType, colName))
}
),
// Convert to a table for readability
MetadataTable = Table.FromRows(
List.Transform(
MetadataByColumn,
(pair) => {
pair{0},
Record.FieldOrDefault(pair{1}, "Description", "(no description)"),
Record.FieldOrDefault(pair{1}, "Nullable", null),
Record.FieldOrDefault(pair{1}, "BusinessOwner", "(unassigned)")
}
),
{"Column", "Description", "Nullable", "BusinessOwner"}
)
in
MetadataTable
This produces a clean documentation table for the entire dataset — automatically generated from the type annotations you've embedded in the query. Load this to a worksheet tab named "Schema Documentation" and you've got living, self-updating data documentation.
Here's where everything comes together into something you can actually use in production. The idea is a reusable validation function that reads a table's type metadata and checks whether the actual data conforms to the rules embedded in that metadata.
We'll build a function called ValidateTable that checks three things:
AllowedValues metadata contain only the specified valueslet
ValidateTable = (tbl as table) as table =>
let
TableType = Value.Type(tbl),
ColumnNames = Table.ColumnNames(tbl),
// Check each column's metadata for validation rules
ValidationResults = List.Transform(
ColumnNames,
(colName) =>
let
ColType = Type.TableColumn(TableType, colName),
ColMeta = Value.Metadata(ColType),
// Check Nullable rule
IsNullable = Record.FieldOrDefault(ColMeta, "Nullable", true),
NullCount = if not IsNullable then
List.Count(List.Select(Table.Column(tbl, colName), each _ = null))
else
0,
NullableViolation = if NullCount > 0 then
"Column '" & colName & "' has " & Text.From(NullCount) & " null values (marked non-nullable)"
else
null,
// Check AllowedValues rule
AllowedValues = Record.FieldOrDefault(ColMeta, "AllowedValues", null),
InvalidValues = if AllowedValues <> null then
List.Distinct(
List.Select(
Table.Column(tbl, colName),
each not List.Contains(AllowedValues, _)
)
)
else
{},
AllowedValuesViolation = if List.Count(InvalidValues) > 0 then
"Column '" & colName & "' contains invalid values: " &
Text.Combine(List.Transform(InvalidValues, Text.From), ", ")
else
null,
// Collect all violations for this column
Violations = List.Select(
{NullableViolation, AllowedValuesViolation},
each _ <> null
)
in
Violations
),
// Flatten all violations into a single list
AllViolations = List.Combine(ValidationResults),
// If violations exist, surface them as metadata on the table
// rather than throwing an error (allows downstream handling)
ValidationMeta = [
ValidationPassed = List.Count(AllViolations) = 0,
ViolationCount = List.Count(AllViolations),
Violations = AllViolations,
ValidatedAt = DateTime.LocalNow()
],
ResultTable = Value.ReplaceMetadata(
tbl,
Record.Combine({Value.Metadata(tbl), ValidationMeta})
)
in
ResultTable
in
ValidateTable
Now in any query that loads data, you apply the validator and then check the result:
let
Source = /* your data loading steps */,
TypedTable = Value.ReplaceType(Source, AnnotatedTableType),
Validated = ValidateTable(TypedTable),
// Read validation results from metadata
ValidationResult = Value.Metadata(Validated),
// Optionally surface violations as a separate output
// or use error() to halt the pipeline on failure
CheckResult = if not ValidationResult[ValidationPassed] then
error Error.Record(
"SchemaValidationFailed",
"Data does not conform to schema: " &
Text.Combine(ValidationResult[Violations], "; "),
ValidationResult
)
else
Validated
in
CheckResult
Tip: The choice between surfacing violations as metadata vs. throwing an error is a design decision. In a refresh that runs unattended overnight, you may prefer the error to halt processing and trigger a notification. In an interactive QA step, surfacing violations as metadata lets a user review them before deciding whether to proceed.
One of the trickier aspects of working with metadata is that most M transformations don't preserve it. When you use Table.SelectColumns, Table.AddColumn, Table.Filter, or almost any other table function, the result is a new table value — and unless you explicitly re-attach the metadata, it's gone.
Here's a pattern for building metadata-preserving wrappers:
let
// A metadata-preserving version of Table.SelectColumns
SelectColumnsPreserveMeta = (tbl as table, columns as list) as table =>
let
OriginalMeta = Value.Metadata(tbl),
Result = Table.SelectColumns(tbl, columns),
// Re-attach original table metadata (column-level metadata
// from the type is preserved if the type is preserved by the operation)
ResultWithMeta = Value.ReplaceMetadata(Result, OriginalMeta)
in
ResultWithMeta,
// Usage
SourceTable = /* your annotated table */,
Subset = SelectColumnsPreserveMeta(SourceTable, {"OrderID", "Revenue", "Region"})
in
Value.Metadata(Subset)
// Returns the original table-level metadata
For column-level type metadata, the situation is different. When you select a subset of columns, M preserves the type information for those columns if it can infer the type from the original table type. But when you add a computed column or transform a column's values, you'll need to re-annotate the resulting column's type explicitly.
let
// Adding a derived column and preserving/extending type metadata
Source = /* TypedTable from earlier */,
// Add a derived column
WithMargin = Table.AddColumn(Source, "MarginPct",
each [Revenue] / ([Quantity] * [UnitPrice]),
type number
),
// The new column has no metadata on its type — annotate it now
BaseType = Value.Type(WithMargin),
MarginType = type number meta [
Description = "Gross margin as a percentage of gross revenue",
Formula = "Revenue / (Quantity * UnitPrice)",
Nullable = false,
BusinessOwner = "Finance"
],
// Replace the table type with an updated version that includes
// the annotated MarginPct column
EnrichedType = Type.ReplaceFacets(
BaseType,
// We have to rebuild — M doesn't have a direct "update one column's type" function
// so we use Table.Schema + custom logic, or a helper approach:
[] // placeholder — see the full helper pattern below
)
in
WithMargin
Because M doesn't expose a single function to update one column's type annotation within a table type, experienced M developers often maintain a type registry record — a record that maps column names to their annotated types — and rebuild the full table type from it whenever the schema changes:
let
// Type registry — the single source of truth for column type annotations
TypeRegistry = [
OrderID = type number meta [Description = "Unique order identifier", Nullable = false],
OrderDate = type date meta [Description = "Date order was placed", Nullable = false],
Region = type text meta [Description = "Sales region", AllowedValues = {"AMER","EMEA","APAC"}],
Revenue = type number meta [Description = "Net revenue in USD", Nullable = false, Currency = "USD"],
MarginPct = type number meta [Description = "Gross margin %", Formula = "Revenue/(Qty*Price)"]
],
// Function to build a table type from the registry
BuildTableType = (registry as record) as type =>
let
Fields = Record.FieldNames(registry),
TypeFields = List.Transform(Fields, (f) => {f, Record.Field(registry, f)}),
// Type.ForRecord constructs a record type; for tables we use the table type literal approach
TableTypeDef = Type.ForTable(Type.ForRecord(
Record.FromList(
List.Transform(Fields, (f) => Record.Field(registry, f)),
Fields
),
false
))
in
TableTypeDef,
// Apply registry to a table
Source = /* your data */,
AnnotatedTable = Value.ReplaceType(Source, BuildTableType(TypeRegistry))
in
AnnotatedTable
Note:
Type.ForTableandType.ForRecordare part of M's type construction API. They're not commonly documented but are stable functions available in the M engine. The pattern above gives you a maintainable single-source-of-truth for all column annotations.
Let's pull everything together into a complete, production-realistic scenario. You're building a sales data pipeline that:
Create a new Power Query query called SalesPipeline with the following content:
let
// =========================================================
// 1. TYPE REGISTRY — single source of truth
// =========================================================
TypeRegistry = [
OrderID = type number meta [Description = "Unique order ID", Nullable = false],
OrderDate = type date meta [Description = "Order placement date", Nullable = false],
CustomerID = type number meta [Description = "Customer identifier", Nullable = false],
Region = type text meta [
Description = "Sales region code",
AllowedValues = {"AMER", "EMEA", "APAC"},
Nullable = false
],
Product = type text meta [Description = "Product SKU", Nullable = false],
Quantity = type number meta [Description = "Units ordered", Nullable = false],
UnitPrice = type number meta [Description = "Price per unit USD", Nullable = false, Currency = "USD"],
Revenue = type number meta [Description = "Net revenue USD", Nullable = false, Currency = "USD"]
],
// =========================================================
// 2. LOAD RAW DATA
// =========================================================
RawSource = Csv.Document(
File.Contents("C:\Data\sales_2024_q2.csv"),
[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(RawSource, [PromoteAllScalars=true]),
TypedTable = Table.TransformColumnTypes(PromotedHeaders, {
{"OrderID", Int64.Type},
{"OrderDate", type date},
{"CustomerID", Int64.Type},
{"Region", type text},
{"Product", type text},
{"Quantity", Int64.Type},
{"UnitPrice", type number},
{"Revenue", type number}
}),
// =========================================================
// 3. ATTACH TYPE ANNOTATIONS FROM REGISTRY
// =========================================================
AnnotatedType = type table [
OrderID = TypeRegistry[OrderID],
OrderDate = TypeRegistry[OrderDate],
CustomerID = TypeRegistry[CustomerID],
Region = TypeRegistry[Region],
Product = TypeRegistry[Product],
Quantity = TypeRegistry[Quantity],
UnitPrice = TypeRegistry[UnitPrice],
Revenue = TypeRegistry[Revenue]
],
AnnotatedTable = Value.ReplaceType(TypedTable, AnnotatedType),
// =========================================================
// 4. ATTACH PIPELINE LINEAGE METADATA TO THE TABLE
// =========================================================
PipelineMeta = [
QueryName = "SalesPipeline",
SourceFile = "sales_2024_q2.csv",
SchemaVersion = "2.1",
Owner = "Analytics Engineering",
LoadedAt = DateTime.LocalNow(),
RowCount = Table.RowCount(AnnotatedTable)
],
TaggedTable = AnnotatedTable meta PipelineMeta,
// =========================================================
// 5. VALIDATE AGAINST SCHEMA RULES
// =========================================================
Validate = (tbl as table) as table =>
let
TblType = Value.Type(tbl),
ColNames = Table.ColumnNames(tbl),
Violations = List.Combine(List.Transform(ColNames, (col) =>
let
CMeta = Value.Metadata(Type.TableColumn(TblType, col)),
Nullable = Record.FieldOrDefault(CMeta, "Nullable", true),
Allowed = Record.FieldOrDefault(CMeta, "AllowedValues", null),
NullV = if not Nullable then
let nc = List.Count(List.Select(Table.Column(tbl,col), each _=null))
in if nc > 0 then {"NULL violation: '" & col & "' has " & Text.From(nc) & " nulls"} else {}
else {},
AllowedV = if Allowed <> null then
let bad = List.Distinct(List.Select(Table.Column(tbl,col), each not List.Contains(Allowed,_)))
in if List.Count(bad)>0 then {"VALUE violation: '" & col & "' contains " & Text.Combine(List.Transform(bad,Text.From),", ")} else {}
else {}
in List.Combine({NullV, AllowedV}))),
VMeta = [ValidationPassed = List.Count(Violations)=0, Violations = Violations, ValidatedAt = DateTime.LocalNow()]
in
Value.ReplaceMetadata(tbl, Record.Combine({Value.Metadata(tbl), VMeta})),
ValidatedTable = Validate(TaggedTable),
ValidationInfo = Value.Metadata(ValidatedTable),
// =========================================================
// 6. HALT ON VALIDATION FAILURE (or swap for soft warning)
// =========================================================
FinalTable = if not ValidationInfo[ValidationPassed] then
error Error.Record(
"SchemaValidationFailed",
"Pipeline halted — " & Text.From(List.Count(ValidationInfo[Violations])) & " violation(s) detected.",
ValidationInfo
)
else
ValidatedTable
in
FinalTable
Create a second query called SalesPipeline_Schema that generates the documentation table:
let
Source = SalesPipeline,
TblType = Value.Type(Source),
ColNames = Table.ColumnNames(Source),
SchemaRows = List.Transform(ColNames, (col) =>
let
CMeta = Value.Metadata(Type.TableColumn(TblType, col))
in {
col,
Record.FieldOrDefault(CMeta, "Description", "(none)"),
Record.FieldOrDefault(CMeta, "Nullable", true),
Record.FieldOrDefault(CMeta, "Currency", null),
Record.FieldOrDefault(CMeta, "AllowedValues", null)
}),
SchemaTable = Table.FromRows(SchemaRows, {"Column", "Description", "Nullable", "Currency", "AllowedValues"}),
// Attach pipeline lineage from the parent query
PipelineMeta = Value.Metadata(Source)
in
SchemaTable meta PipelineMeta
Load SalesPipeline to your data model and SalesPipeline_Schema to a worksheet. Every time the workbook refreshes, the schema documentation updates automatically. Every column description, nullable flag, and allowed values constraint lives in one place — the type registry at the top of SalesPipeline — and flows through to both the validation layer and the documentation output.
Work through this sequence to cement the concepts:
Setup: Create a new Power Query query. Paste in the following table using #table:
let
SampleData = #table(
{"EmployeeID", "Department", "Salary", "StartDate", "Active"},
{
{1001, "Engineering", 95000, #date(2019,3,15), true},
{1002, "Marketing", 72000, #date(2021,7,1), true},
{1003, "Engineering", 88000, #date(2020,11,20),true},
{1004, "HR", 64000, #date(2018,5,10), false},
{1005, "Finance", null, #date(2022,9,5), true}
}
)
in
SampleData
Tasks:
Define a type registry record with at minimum: Description, Nullable, and AllowedValues metadata for each column. For Department, set AllowedValues to {"Engineering", "Marketing", "HR", "Finance", "Operations"}. For Salary, set Nullable = false.
Apply the annotated table type using Value.ReplaceType.
Attach table-level metadata including Owner, LoadedAt, and a RowCount.
Write a validation step (or adapt the ValidateTable function from earlier) and confirm it catches the null Salary in row 5.
Generate a schema documentation table from the annotated type, showing column name, description, and nullable status.
Challenge: Modify the validator to also check that Salary values, when not null, are greater than 30000. You'll need to add a custom MinValue metadata field and extend the validation logic to handle numeric range checks.
"My metadata disappeared after a transformation"
The most common issue. Standard M table functions return new values with no metadata. After any significant transformation step — Table.SelectColumns, Table.AddColumn, Table.Join, etc. — you must explicitly re-attach metadata. Build helper wrapper functions for the transformations you use most, as shown in the "Preserving Metadata" section.
"Value.Metadata returns [] even though I attached metadata"
Check whether you used the meta keyword on the table vs. on the type. table meta [...] attaches metadata to the value. type table [...] meta [...] attaches metadata to the type. Reading the former with Value.Metadata(table) works. Reading type-level metadata requires Value.Metadata(Value.Type(table)). These are different locations.
"Type.TableColumn throws an error on column X"
This happens when the table's type doesn't include an explicit definition for that column — the column exists in the data but not in the table type. This occurs when you haven't used Value.ReplaceType with a fully-specified table type, or when you've added columns after type annotation. Check Value.Type(yourTable) and inspect whether it's an open or closed table type.
"My AllowedValues check is flagging nulls as violations"
When a column contains nulls and you check not List.Contains(AllowedValues, _), null values will fail the test because null isn't in the list. Fix this by modifying the filter: each _ <> null and not List.Contains(AllowedValues, _). If you want to catch nulls separately (via the Nullable check), this keeps the two rules clean.
"Error.Record in the validation step isn't showing detail in Power BI Service"
Power BI Service surfaces the error reason string but may truncate the detail record. For production pipelines that need to communicate failure details to a monitoring system, consider writing violation records to a separate table output (using the soft-warning pattern) rather than throwing. A dedicated ValidationLog query that loads to a table is often more operationally useful than an error that halts the refresh.
"Performance is slow when validating large tables"
The validation function calls Table.Column once per validated column, which is generally efficient. The real cost is List.Select for null checks and value comparisons on large columns. For tables over 1 million rows, consider whether full validation on every refresh is necessary, or whether you can validate only the first 10,000 rows as a sampling strategy: Table.FirstN(tbl, 10000) passed to the validator covers most schema drift scenarios with a fraction of the compute.
You've now moved from understanding metadata as a concept to using it as a structural component of your M pipelines. The key ideas to carry forward:
meta, Value.Metadata, and Value.ReplaceMetadata are the core tools for working with value-level metadata. Value.ReplaceType is how you stamp column-level annotations onto a table.Where to go next:
Table.Schema: Table.Schema returns a rich base schema table. Combine it with your custom metadata extraction to build comprehensive data dictionaries that include both the engine-inferred type information and your semantic annotations.AllowedValues, Nullable, and range rules from a configuration table (loaded from a SharePoint list or database) rather than hardcoding them in the type registry. This gives business users the ability to update validation rules without touching M code.Value.Metadata mechanism is the same one used to document custom functions in the Power Query UI. Adding a Documentation.Name, Documentation.LongDescription, and Documentation.Examples metadata record to a function value makes it show up with proper help text in the function editor — which is a significant quality-of-life improvement for any shared function library.The goal of all of this is the same: queries that tell you what they expect, check whether they got it, and leave a trail of breadcrumbs for whoever comes next. That's what separates a pipeline that works from a pipeline that's maintainable.
Learning Path: Advanced M Language