Mastering M Language Metadata: Attaching, Reading, and Leveraging Type Annotations for Robust Data Pipelines in Power Query

Introduction

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:

• How M's metadata system works conceptually and why it's separate from the value itself
• How to attach, read, and update metadata on any M value using Value.ReplaceMetadata, Value.Metadata, and type annotations
• How to use custom type records to carry semantic information through multi-step transformations
• How to build a metadata-driven validation layer that catches schema drift before it breaks downstream reports
• How to document a query's schema programmatically so the next person doesn't spend half their day debugging what you built

---

Prerequisites

This lesson assumes you're comfortable with:

• Writing and reading M expressions beyond the Power Query UI (including let...in blocks and custom functions)
• Working with records, lists, and tables in M
• Basic type system concepts: type text, type number, table type definitions
• The Table.TransformColumnTypes and Table.Schema functions

If records or custom functions feel shaky, revisit those fundamentals first. Everything in this lesson builds on them.

---

How M Metadata Actually Works

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.

---

Reading Metadata with Value.Metadata

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.Metadata on a value with no metadata returns [], not null. Test for empty metadata with Value.Metadata(x) = [] rather than Value.Metadata(x) = null.

---

Replacing and Merging Metadata

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 meta in a chain without merging is one of the most common metadata bugs. If you write someValue meta [NewField = x] and someValue already had metadata, you've silently discarded all the original annotations. Always merge unless you deliberately want to replace everything.

---

Metadata on Tables and Columns

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.

Annotating a Table

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.

Annotating Individual Columns

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.

---

Reading Column Metadata Programmatically

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.

---

Building a Metadata-Driven Validation Layer

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:

1. All required columns are present
2. Non-nullable columns have no null values
3. Columns with AllowedValues metadata contain only the specified values

let
    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.

---

Preserving Metadata Through Transformations

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.ForTable and Type.ForRecord are 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.

---

Real-World Project: A Self-Documenting Sales Pipeline

Let's pull everything together into a complete, production-realistic scenario. You're building a sales data pipeline that:

1. Loads data from a CSV source
2. Validates it against a declared schema
3. Produces a documentation table automatically
4. Tags each batch with lineage metadata

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.

---

Hands-On Exercise

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:

1. 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.

2. Apply the annotated table type using Value.ReplaceType.

3. Attach table-level metadata including Owner, LoadedAt, and a RowCount.

4. Write a validation step (or adapt the ValidateTable function from earlier) and confirm it catches the null Salary in row 5.

5. Generate a schema documentation table from the annotated type, showing column name, description, and nullable status.

6. 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.

---

Common Mistakes & Troubleshooting

"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.

---

Summary & Next Steps

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:

• M metadata is a parallel record attached to a value. It's inert in computations but accessible to your logic and to functions that know how to read it.
• 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.
• Type registries give you a single, maintainable source of truth for schema annotations that can feed both validation and documentation automatically.
• Most standard M transformations don't preserve metadata — you must actively design for preservation with wrapper patterns.

Where to go next:

• Custom connectors: If you're building Power Query connectors, metadata is how you surface UI hints, documentation strings, and parameter constraints in the Power Query dialog. The M connector SDK uses the same metadata mechanism, just read by the connector host rather than your own functions.
• Combining with 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.
• Dynamic validation from config tables: The next evolution of the validation pattern is reading your 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.
• Metadata in function documentation: The 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.