You've been there. A downstream report breaks on a Monday morning because someone upstream changed a column name from CustomerID to Customer_ID, or a finance system started sending revenue figures as text strings instead of decimals, or a new field appeared in the source with the same name as an existing calculated column. These aren't exotic edge cases — they're the everyday reality of connecting to systems you don't control. And every time it happens, you're the one who gets the call.
The standard Power Query approach to this problem is essentially "hope it works." You build your transformation steps, hit Refresh, and find out at runtime whether the source still looks like what you expected. If it doesn't, you get a cryptic error buried three steps into a nested query, and you spend an hour figuring out which upstream system changed what. This is not a data quality strategy. It's a debugging strategy dressed up as one.
By the end of this lesson, you'll be able to build something substantially better: a schema validation and type enforcement pipeline that acts as a formal contract between your data source and your transformation logic. Your queries will know what they expect, verify that they got it, and fail loudly and informatively when they didn't — rather than silently corrupting downstream results or throwing confusing errors deep in your transformation chain.
What you'll learn:
Table.Schema works internally and what metadata it actually exposes about your columnsThis lesson is firmly in expert territory. You should already be comfortable with:
(x) => ... syntax, function parameters with types)List.Transform, List.Select, Record.Field, Table.TransformColumnTypes, and similar standard library functionstype table, type record, nullable types)If any of those feel shaky, spend some time in the Intermediate M Language path first. You'll get more out of this lesson with that foundation solid.
Before you can validate a schema, you need to understand what schema information is available to you in M. The Table.Schema function is the foundation here, and it's more capable than most practitioners realize.
Call Table.Schema on any table and it returns a table with one row per column. Here's what a typical result looks like for a sales transactions table:
Name Position TypeName Kind IsNullable NumericPrecision NumericScale
OrderID 0 Int64.Type number FALSE 19 0
OrderDate 1 Date.Type date FALSE null null
CustomerID 2 Text.Type text TRUE null null
Revenue 3 Decimal.Type number FALSE 18 2
Status 4 Text.Type text TRUE null null
The columns you care most about for validation are:
Name: The column name as a text string. This is your primary contract anchor.
TypeName: A text representation of the M type. This is what you'll compare against your expected type definitions.
Kind: A broader category — "number", "text", "date", "datetime", "logical", "binary", "record", "list", "table", "type", "function", "null", "any". Useful when you want to validate at a coarser granularity than the specific type.
IsNullable: Boolean indicating whether the column allows null values.
NumericPrecision and NumericScale: Populated for numeric types. Useful when you're validating data from SQL sources where the precision/scale is meaningful.
There's an important nuance here: Table.Schema reflects the declared type of the column, not necessarily what the data actually contains. If Power Query loaded a CSV and has Any.Type for every column (which it often does without explicit typing), Table.Schema will show "Any.Type" for all of them even if every value in a column happens to be a number. This distinction matters a lot for your validation strategy — you'll need to validate both declared types (from schema metadata) and actual value types (which requires sampling or attempting coercions).
Here's a simple demonstration of pulling schema info for a real table:
let
// Simulate a source table — in practice this would be your actual source
Source = Table.FromRecords({
[OrderID = 1001, OrderDate = #date(2024,1,15), CustomerID = "CUST-001", Revenue = 1250.00],
[OrderID = 1002, OrderDate = #date(2024,1,16), CustomerID = "CUST-002", Revenue = 875.50]
}),
// Apply explicit types to simulate a properly-typed source
TypedSource = Table.TransformColumnTypes(Source, {
{"OrderID", Int64.Type},
{"OrderDate", Date.Type},
{"CustomerID", Text.Type},
{"Revenue", Currency.Type}
}),
// Now inspect the schema
SchemaInfo = Table.Schema(TypedSource)
in
SchemaInfo
Run this and examine the output carefully. Notice that TypeName shows values like "Int64.Type" — these are the string representations you'll use in your contract definitions.
Key Insight: The
TypeNamecolumn fromTable.Schemauses the M type name as a string —"Int64.Type","Text.Type","Date.Type","Currency.Type", etc. These strings are your comparison keys when building contracts. Don't confuse them with the actual M type values (which you access asInt64.Type,Text.Type, etc. without quotes).
A schema contract is a declarative specification of what you expect from a data source. Rather than scattered Table.TransformColumnTypes calls embedded in your transformation logic, you define the contract once, explicitly, and then your pipeline either validates against it or enforces it.
Here's the design principle: a contract should be the single source of truth about what a column is supposed to be. When something breaks, you should be able to look at the contract and immediately know what was expected, then look at the error report and immediately know what arrived.
A well-designed contract record for a column looks like this:
[
Name = "Revenue", // Required: the column name
Type = Currency.Type, // Required: the expected M type
TypeName = "Currency.Type", // Required: string form for comparison
Nullable = false, // Optional: nullability expectation
Required = true, // Optional: whether absence of column is fatal
Description = "Net revenue after discounts, in USD" // Optional: documentation
]
And a full schema contract is a list of these records:
let
SalesOrderContract = {
[Name = "OrderID", Type = Int64.Type, TypeName = "Int64.Type", Nullable = false, Required = true, Description = "Surrogate key for order"],
[Name = "OrderDate", Type = Date.Type, TypeName = "Date.Type", Nullable = false, Required = true, Description = "Date order was placed"],
[Name = "CustomerID", Type = Text.Type, TypeName = "Text.Type", Nullable = true, Required = true, Description = "Customer identifier"],
[Name = "Revenue", Type = Currency.Type, TypeName = "Currency.Type", Nullable = false, Required = true, Description = "Net revenue in USD"],
[Name = "Status", Type = Text.Type, TypeName = "Text.Type", Nullable = true, Required = false, Description = "Order status (optional field)"]
}
in
SalesOrderContract
Notice a few design decisions here. First, we store both Type (the actual M type value) and TypeName (its string representation). This redundancy serves a purpose: you use TypeName for comparison against Table.Schema output, and you use Type when actually applying Table.TransformColumnTypes. Having both in the contract avoids the need to reconstruct one from the other.
Second, Required = false means the column may or may not be present in the source — your pipeline will skip validation for it if it's absent. Required = true means its absence is a contract violation that should be reported as an error.
Third, Nullable being false doesn't mean you'll reject rows with nulls immediately — it means you'll report a contract violation if the column's declared nullability doesn't match. You can choose to treat this as a warning or an error depending on your use case.
Architecture Note: In a multi-team environment, these contract definitions should live in a dedicated query (not a table query) so they can be referenced by multiple downstream queries. Name it something like
SalesOrder_SchemaContractand keep it in a clearly labeled query group. This way, when the contract changes, you change it in one place.
Now that you have a contract structure, you need a function that takes an actual table and a contract, compares them, and produces a structured validation report. The key design goal here is informative failure — when validation fails, the report should tell you exactly what's wrong, not just that something is wrong.
Let's build this step by step.
let
ValidateTableSchema = (ActualTable as table, Contract as list) as record =>
let
// Get the actual schema as a table
ActualSchema = Table.Schema(ActualTable),
// Convert to a list of records for easier lookup
ActualColumns = Table.ToRecords(ActualSchema),
// Build a lookup record: column name -> schema record
// This makes O(1) lookup by name instead of scanning the list each time
ActualColumnLookup = Record.FromList(
ActualColumns,
List.Transform(ActualColumns, each _[Name])
)
in
ActualColumnLookup
in
ValidateTableSchema
Wait — that's not quite right. Record.FromList expects a list of values and a list of field names, but the values here are already records. Let me correct this and build the full function properly:
let
// Helper: build a column lookup from schema info
BuildColumnLookup = (SchemaTable as table) as record =>
let
Rows = Table.ToRecords(SchemaTable),
Names = List.Transform(Rows, each _[Name]),
// Record.FromList pairs each value with its field name
Lookup = Record.FromList(Rows, Names)
in
Lookup
in
BuildColumnLookup
This gives you a record like [OrderID = [Name="OrderID", Position=0, TypeName="Int64.Type", ...], OrderDate = [...], ...]. Now you can access any column's metadata with Lookup[ColumnName] instead of searching a list each time.
let
CheckColumnPresence = (ActualColumnNames as list, Contract as list) as record =>
let
// Columns that the contract requires
RequiredColumns = List.Transform(
List.Select(Contract, each _[Required] = true),
each _[Name]
),
// All contracted columns (required or optional)
ContractedColumns = List.Transform(Contract, each _[Name]),
// Missing: required columns not present in actual
MissingRequired = List.Select(
RequiredColumns,
each not List.Contains(ActualColumnNames, _)
),
// Missing optional: optional columns not present (informational only)
MissingOptional = List.Select(
List.Select(ContractedColumns, (c) =>
List.Contains(
List.Transform(List.Select(Contract, each _[Name] = c), each _[Required]),
false
)
),
each not List.Contains(ActualColumnNames, _)
),
// Extra: columns present in actual but not in contract at all
ExtraColumns = List.Select(
ActualColumnNames,
each not List.Contains(ContractedColumns, _)
)
in
[
MissingRequired = MissingRequired,
MissingOptional = MissingOptional,
ExtraColumns = ExtraColumns
]
in
CheckColumnPresence
Note on Extra Columns: Whether extra columns are a validation failure depends on your contract philosophy. In a strict contract model, unexpected columns are a red flag — they might indicate a schema migration is in progress and you're seeing transitional data. In a lenient contract model, extra columns are harmless and you just ignore them. We'll make this configurable in the final function.
let
CheckColumnTypes = (ActualLookup as record, Contract as list, ActualColumnNames as list) as list =>
let
// Only check types for columns that are actually present
PresentContractColumns = List.Select(
Contract,
each List.Contains(ActualColumnNames, _[Name])
),
// For each present contracted column, check if type matches
TypeChecks = List.Transform(
PresentContractColumns,
(ContractCol) =>
let
ColName = ContractCol[Name],
ExpectedTypeName = ContractCol[TypeName],
// Get actual type info — need to handle missing field gracefully
ActualColInfo = Record.Field(ActualLookup, ColName),
ActualTypeName = ActualColInfo[TypeName],
TypeMatches = ActualTypeName = ExpectedTypeName,
// Also check nullable if contract specifies it
NullCheck = if Record.HasFields(ContractCol, "Nullable") then
let
ExpectedNullable = ContractCol[Nullable],
ActualNullable = ActualColInfo[IsNullable],
NullableMatches = ActualNullable = ExpectedNullable
in
NullableMatches
else
true
in
[
ColumnName = ColName,
ExpectedType = ExpectedTypeName,
ActualType = ActualTypeName,
TypeValid = TypeMatches,
NullableValid = NullCheck,
IsValid = TypeMatches and NullCheck
]
)
in
TypeChecks
in
CheckColumnTypes
Now let's bring it all together into a single, composable validation function that returns a structured report:
let
ValidateTableSchema = (ActualTable as table, Contract as list, optional Options as record) as record =>
let
// Default options
DefaultOptions = [
StrictExtraColumns = false, // Treat extra columns as error
FailOnFirstError = false // Return full report vs. throw on first issue
],
ResolvedOptions = if Options = null then DefaultOptions
else Record.Combine({DefaultOptions, Options}),
// --- Step 1: Get actual schema info ---
ActualSchema = Table.Schema(ActualTable),
ActualSchemaRows = Table.ToRecords(ActualSchema),
ActualColumnNames = List.Transform(ActualSchemaRows, each _[Name]),
// Build lookup for O(1) access
ActualLookup = Record.FromList(ActualSchemaRows, ActualColumnNames),
// --- Step 2: Check column presence ---
RequiredContractCols = List.Transform(
List.Select(Contract, each _[Required] = true),
each _[Name]
),
AllContractCols = List.Transform(Contract, each _[Name]),
MissingRequired = List.Select(
RequiredContractCols,
each not List.Contains(ActualColumnNames, _)
),
ExtraColumns = List.Select(
ActualColumnNames,
each not List.Contains(AllContractCols, _)
),
// --- Step 3: Check types for present columns ---
PresentContractCols = List.Select(
Contract,
each List.Contains(ActualColumnNames, _[Name])
),
TypeViolations = List.Select(
List.Transform(
PresentContractCols,
(ContractCol) =>
let
ColName = ContractCol[Name],
ExpectedTypeName = ContractCol[TypeName],
ActualColInfo = Record.Field(ActualLookup, ColName),
ActualTypeName = ActualColInfo[TypeName],
TypeMatches = ActualTypeName = ExpectedTypeName,
ExpectedNullable = if Record.HasFields(ContractCol, "Nullable")
then ContractCol[Nullable]
else null,
ActualNullable = ActualColInfo[IsNullable],
NullableMatches = if ExpectedNullable = null then true
else ActualNullable = ExpectedNullable
in
[
ColumnName = ColName,
ExpectedType = ExpectedTypeName,
ActualType = ActualTypeName,
TypeMatches = TypeMatches,
ExpectedNullable = ExpectedNullable,
ActualNullable = ActualNullable,
NullableMatches = NullableMatches,
IsValid = TypeMatches and NullableMatches
]
),
each not _[IsValid]
),
// --- Step 4: Determine overall validity ---
HasMissingRequired = List.Count(MissingRequired) > 0,
HasTypeViolations = List.Count(TypeViolations) > 0,
HasExtraColumns = List.Count(ExtraColumns) > 0,
IsValid = not HasMissingRequired
and not HasTypeViolations
and (not HasExtraColumns or not ResolvedOptions[StrictExtraColumns]),
// --- Step 5: Build the report ---
ValidationReport = [
IsValid = IsValid,
ValidatedAt = DateTime.LocalNow(),
SourceRowCount = Table.RowCount(ActualTable),
SourceColumnCount = List.Count(ActualColumnNames),
ContractColumnCount = List.Count(AllContractCols),
MissingRequiredColumns = MissingRequired,
TypeViolations = TypeViolations,
ExtraColumns = ExtraColumns,
Summary = if IsValid then "PASS: Table conforms to schema contract"
else Text.Combine(
List.RemoveNulls({
if HasMissingRequired then "MISSING_COLUMNS: " & Text.Combine(MissingRequired, ", ") else null,
if HasTypeViolations then "TYPE_VIOLATIONS: " & Text.Combine(List.Transform(TypeViolations, each _[ColumnName] & " (expected " & _[ExpectedType] & ", got " & _[ActualType] & ")"), "; ") else null,
if HasExtraColumns and ResolvedOptions[StrictExtraColumns] then "EXTRA_COLUMNS: " & Text.Combine(ExtraColumns, ", ") else null
}),
" | "
)
]
in
if ResolvedOptions[FailOnFirstError] and not IsValid
then error ValidationReport[Summary]
else ValidationReport
in
ValidateTableSchema
This is your core validation engine. Notice it returns a record by default, not an error — this means you can inspect the report in Power Query's preview, log it, route different actions based on it, or surface it in a data quality dashboard. Passing FailOnFirstError = true turns it into a hard gate.
Validation tells you what's wrong. Enforcement fixes what's wrong — or at least tries to. Type enforcement is a separate concern from validation, and it's worth being explicit about when you do each.
The enforcement pipeline's job is to take your source table and your contract, and produce a table that conforms to the contract's type expectations — coercing where possible, flagging where not.
Real-world type mismatches usually fall into a few categories:
Any.Type, but the values are actually the right thing. Just cast."1,250.00" from a legacy system. Needs cleaning, then casting."2024-01-15". Needs Date.FromText.Your enforcement function needs to handle the first three confidently and flag the fourth and fifth:
let
EnforceColumnType = (ColumnValue as any, TargetType as type, TargetTypeName as text) as any =>
let
// Null passthrough — let the table handle nullable validation separately
Result = if ColumnValue = null then null
else
try
// Attempt direct conversion to target type
let
Converted = Value.As(ColumnValue, TargetType)
in
Converted
otherwise
// Direct conversion failed — try type-specific coercions
if TargetTypeName = "Int64.Type" then
try Int64.From(ColumnValue) otherwise null
else if TargetTypeName = "Decimal.Type" or TargetTypeName = "Currency.Type" then
try Decimal.From(ColumnValue) otherwise null
else if TargetTypeName = "Date.Type" then
try Date.From(ColumnValue) otherwise null
else if TargetTypeName = "DateTime.Type" then
try DateTime.From(ColumnValue) otherwise null
else if TargetTypeName = "Text.Type" then
try Text.From(ColumnValue) otherwise null
else if TargetTypeName = "Logical.Type" then
try Logical.From(ColumnValue) otherwise null
else
null // Unsupported target type, can't coerce
in
Result
in
EnforceColumnType
Warning:
Value.Asis an underdocumented M function. It attempts to reinterpret a value as a given type and will succeed if the value is already compatible. It's different from conversion functions likeInt64.From— it doesn't parse text into numbers, it just checks type compatibility and coerces at the type system level. Use it as your first attempt, then fall back to explicit converters.
let
EnforceTableSchema = (ActualTable as table, Contract as list, optional Options as record) as table =>
let
DefaultOptions = [
DropExtraColumns = false, // Remove columns not in contract
AddMissingOptional = false, // Add missing optional columns as null
CoerceTypes = true // Attempt type coercions
],
ResolvedOptions = if Options = null then DefaultOptions
else Record.Combine({DefaultOptions, Options}),
ActualColumnNames = Table.ColumnNames(ActualTable),
AllContractCols = List.Transform(Contract, each _[Name]),
// --- Step 1: Drop extra columns if option enabled ---
Step1 = if ResolvedOptions[DropExtraColumns] then
Table.SelectColumns(
ActualTable,
List.Intersect({ActualColumnNames, AllContractCols})
)
else
ActualTable,
// --- Step 2: Add missing optional columns as null ---
OptionalMissing = List.Select(
List.Select(Contract, each _[Required] = false),
each not List.Contains(Table.ColumnNames(Step1), _[Name])
),
Step2 = if ResolvedOptions[AddMissingOptional] and List.Count(OptionalMissing) > 0 then
List.Fold(
OptionalMissing,
Step1,
(TableAcc, ContractCol) =>
Table.AddColumn(TableAcc, ContractCol[Name], each null, ContractCol[Type])
)
else
Step1,
// --- Step 3: Apply type coercions for present columns ---
PresentContractCols = List.Select(
Contract,
each List.Contains(Table.ColumnNames(Step2), _[Name])
),
Step3 = if ResolvedOptions[CoerceTypes] then
// For each contracted column, apply cell-by-cell coercion
// We use Table.TransformColumns with the EnforceColumnType helper
Table.TransformColumns(
Step2,
List.Transform(
PresentContractCols,
(ContractCol) =>
{
ContractCol[Name],
(val) => EnforceColumnType(val, ContractCol[Type], ContractCol[TypeName]),
ContractCol[Type]
}
)
)
else
// Simple type assignment without coercion — just set declared types
Table.TransformColumnTypes(
Step2,
List.Transform(
PresentContractCols,
each {_[Name], _[Type]}
)
),
// --- Step 4: Reorder columns to match contract order ---
ContractColsPresent = List.Select(
AllContractCols,
each List.Contains(Table.ColumnNames(Step3), _)
),
ExtraColsInOutput = List.Select(
Table.ColumnNames(Step3),
each not List.Contains(AllContractCols, _)
),
FinalColumnOrder = List.Combine({ContractColsPresent, ExtraColsInOutput}),
Step4 = Table.ReorderColumns(Step3, FinalColumnOrder)
in
Step4
in
EnforceTableSchema
Notice that Step3 references EnforceColumnType — in practice, this means EnforceColumnType needs to be defined either as a separate query (accessible by name) or inline before EnforceTableSchema in the same let block.
Now let's put validation and enforcement together into a single pipeline pattern that you'd actually use in production. The pattern looks like this:
let
// =====================================================
// CONTRACT DEFINITION
// =====================================================
SalesOrderContract = {
[Name = "OrderID", Type = Int64.Type, TypeName = "Int64.Type", Nullable = false, Required = true],
[Name = "OrderDate", Type = Date.Type, TypeName = "Date.Type", Nullable = false, Required = true],
[Name = "CustomerID", Type = Text.Type, TypeName = "Text.Type", Nullable = true, Required = true],
[Name = "Revenue", Type = Currency.Type, TypeName = "Currency.Type", Nullable = false, Required = true],
[Name = "Region", Type = Text.Type, TypeName = "Text.Type", Nullable = true, Required = false]
},
// =====================================================
// SOURCE DATA (simulate a real source connection)
// =====================================================
RawSource = Csv.Document(
File.Contents("C:\Data\sales_orders.csv"),
[Delimiter = ",", Columns = 5, Encoding = 1252, QuoteStyle = QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(RawSource, [PromoteAllScalars = true]),
// =====================================================
// VALIDATION PASS
// =====================================================
ValidationReport = ValidateTableSchema(
PromotedHeaders,
SalesOrderContract,
[StrictExtraColumns = false, FailOnFirstError = false]
),
// Hard gate on missing required columns
// If required columns are absent, we can't meaningfully enforce types
// so we surface the error immediately with a useful message
GuardedSource = if List.Count(ValidationReport[MissingRequiredColumns]) > 0
then error Error.Record(
"Schema.ContractViolation",
"Source is missing required columns: " & Text.Combine(ValidationReport[MissingRequiredColumns], ", "),
ValidationReport
)
else PromotedHeaders,
// =====================================================
// ENFORCEMENT PASS
// =====================================================
EnforcedTable = EnforceTableSchema(
GuardedSource,
SalesOrderContract,
[
DropExtraColumns = false,
AddMissingOptional = true,
CoerceTypes = true
]
),
// =====================================================
// ATTACH QUALITY METADATA (optional but recommended)
// =====================================================
// Tag the result with quality metadata so downstream queries
// can make decisions based on it
QualityMetadata = [
ValidationPassedAt = ValidationReport[ValidatedAt],
SourceRowCount = ValidationReport[SourceRowCount],
TypeViolationsFound = List.Count(ValidationReport[TypeViolations]),
TypeViolationDetails = ValidationReport[TypeViolations],
CoercionApplied = List.Count(ValidationReport[TypeViolations]) > 0
]
in
// Return the enforced table as your primary output
// The QualityMetadata record can be loaded separately as a lookup table
// for a data quality dashboard
EnforcedTable
Here's a pattern worth knowing: you can expose QualityMetadata as a separate query that references the same pipeline without re-running the source fetch:
// Query: SalesOrders (returns EnforcedTable from pipeline above)
// Query: SalesOrders_QualityReport (separate query)
let
// Reference the pipeline query — Power Query is smart enough
// not to re-execute the source fetch if it's already cached
Pipeline = SalesOrders_Pipeline, // A query that returns the record {Data, Quality}
QualityData = Pipeline[QualityMetadata]
in
Record.ToTable(QualityData)
To make this work cleanly, you'd restructure your pipeline query to return a record with both the table and the quality metadata, then have two consuming queries that each pull one field.
One of the most common real-world scenarios you'll face: your source comes from a CSV, a SharePoint list, or a Web.Contents call, and Table.Schema shows "Any.Type" for everything. Your type comparison logic would flag every column as a violation even when the data is actually fine.
There are two strategies here:
Strategy 1: Validate After Preliminary Typing
Run a Table.TransformColumnTypes with a "best guess" typing before you run validation. If the preliminary typing succeeds without errors, the data is coercible. If it throws, the data has genuine type problems:
let
PreliminaryTyping =
try Table.TransformColumnTypes(
RawSource,
List.Transform(
SalesOrderContract,
each {_[Name], _[Type]}
)
),
// If preliminary typing succeeded, validate the typed result
// If it failed, the source has data quality issues we need to flag
TypingSucceeded = not PreliminaryTyping[HasError],
TypedForValidation = if TypingSucceeded
then PreliminaryTyping[Value]
else RawSource // Validate untyped, expect violations
in
TypedForValidation
Strategy 2: Skip TypeName Comparison for Any.Type Sources and Rely on Enforcement
If you know your source will always come in untyped, skip the type comparison in validation (since "Any.Type" ≠ "Int64.Type" will always be true) and let enforcement handle the coercion:
// In your CheckColumnTypes logic, add an exemption:
TypeMatches = ActualTypeName = ExpectedTypeName
or ActualTypeName = "Any.Type" // Untyped source, enforcement will handle it
This is a pragmatic compromise. Purists will object, but in practice it's the right call for CSV and web sources where untyped data is the expected condition.
Build a complete schema validation and enforcement pipeline for the following scenario:
Scenario: Your team receives a monthly employee headcount export from the HR system. The file arrives as a CSV with no guaranteed consistency between months — columns sometimes get renamed, occasionally come in wrong types, and some months include bonus columns that aren't relevant to your analysis.
Requirements:
Define a schema contract for the following columns:
EmployeeID (Int64, not nullable, required)Department (Text, nullable, required)HireDate (Date, not nullable, required)Salary (Currency, not nullable, required)ManagerID (Int64, nullable, required)RemoteFlag (Logical, nullable, optional)Build the ValidateTableSchema function and test it against a sample table where:
HireDate arrives as Text.Type (date strings like "2023-03-15")Salary arrives as Any.TypeLegacyCostCenter is present (not in contract)Inspect the validation report. Confirm it correctly identifies the HireDate and Salary type violations but does not flag LegacyCostCenter as an error (since StrictExtraColumns = false).
Run EnforceTableSchema with CoerceTypes = true and confirm that HireDate strings convert correctly to dates and Salary coerces from Any.Type correctly.
Bonus: Modify the contract to mark ManagerID as Required = false and re-run validation against a version of the source that doesn't include the ManagerID column. Confirm the report shows it as missing-optional rather than a hard error.
// WRONG: This compares the actual type value against a string
if ActualTypeName = Int64.Type then ... // Type mismatch error
// RIGHT: Compare string to string
if ActualTypeName = "Int64.Type" then ...
Table.Schema returns TypeName as a text string, not an M type value. If you try to compare a string against Int64.Type (which is an M type value), you'll get a type mismatch error or always-false comparison.
If your CSV import has Any.Type columns, Table.Schema will show "Any.Type" even if every value in the column is a valid integer. Don't assume declared type = data type. Use the preliminary typing strategy described above when working with untyped sources.
// WRONG: Error.Record takes (Reason, Message, Detail) — all must be the right types
error Error.Record("Schema.Violation", SomeRecord, "extra info") // Wrong order
// RIGHT:
error Error.Record("Schema.ContractViolation", "Human-readable message", DetailRecord)
The third argument to Error.Record is the detail, which can be any M value including a record. The second is the message string. Get them in the right order.
// This forces a full table scan to count rows — expensive on large datasets
SourceRowCount = Table.RowCount(ActualTable)
If your source is a large SQL table, including Table.RowCount in your validation report will force the engine to issue a SELECT COUNT(*) query separately from your main data fetch. Either omit it, defer it, or only include it when you know the source can handle it cheaply.
If your source table has duplicate column names (pathological, but it happens with some legacy systems), Record.FromList will fail because record field names must be unique. Add a deduplication step:
// Guard against duplicate column names in source
UniqueNameCheck = List.Distinct(ActualColumnNames),
HasDuplicates = List.Count(UniqueNameCheck) < List.Count(ActualColumnNames),
SafeSource = if HasDuplicates
then error "Source table has duplicate column names: " &
Text.Combine(List.Difference(ActualColumnNames, UniqueNameCheck), ", ")
else ActualTable
M string comparisons are case-sensitive. If your contract says "OrderID" but the source sends "orderid", your validation will report it as missing even though it's clearly the same column.
// In your column presence check, use case-insensitive comparison:
MissingRequired = List.Select(
RequiredContractCols,
(ContractColName) => not List.ContainsAny(
List.Transform(ActualColumnNames, Text.Lower),
{Text.Lower(ContractColName)}
)
)
Whether you want case-insensitive matching depends on your discipline. In enterprise environments, enforcing exact case is often the right call — it catches careless renames. For consumer data, case-insensitive is more practical.
Every time you reference ActualTable in your validation and enforcement functions, Power Query may re-evaluate it. If your source is a live database or API, this means multiple round-trips. Always buffer your source before passing it into the pipeline:
BufferedSource = Table.Buffer(RawSource),
ValidationReport = ValidateTableSchema(BufferedSource, Contract),
EnforcedTable = EnforceTableSchema(BufferedSource, Contract)
Table.Buffer forces evaluation and caches the result in memory, ensuring subsequent references don't trigger re-fetches.
You now have a complete, composable schema validation and type enforcement pipeline for Power Query M. Let's recap the architecture:
The Contract Layer defines what you expect: column names, types, nullability, and whether columns are required or optional. This lives as a list of records, ideally in its own query.
The Validation Layer compares your actual source against the contract and produces a structured report. It distinguishes between missing required columns (hard failure), type mismatches (soft violation, potentially coercible), and extra columns (informational). The report is a record you can inspect, route, or surface in a dashboard.
The Enforcement Layer takes the source and the contract and produces a typed, ordered table that conforms to the contract. It handles the common coercion cases (Any.Type, Text to Numeric, Text to Date) gracefully and falls back to null for genuinely uncoercible values rather than crashing.
The Pipeline Pattern chains validation and enforcement with a hard gate on missing required columns, buffers the source to avoid re-fetching, and optionally produces quality metadata alongside your clean data.
For your next steps, consider:
Revenue must be ≥ 0, Status must be in {"Active", "Closed", "Pending"}). This requires building a value validation layer that operates row-by-row or uses Table.SelectRows and Table.AddColumn to flag violations.QualityMetadata records from multiple pipeline queries into a single monitoring table, giving you visibility across all your data connections.ValidationReport record you're already producing maps naturally to a notification payload.The pattern you've built here isn't just a Power Query technique — it's a data engineering discipline. Treating schema agreements as explicit, machine-verifiable contracts rather than implicit assumptions is how you build data systems that fail informatively instead of silently, and that you can trust enough to build real decisions on.
Learning Path: Advanced M Language