Demonstrates some mechanisms in M for applying or enforcing a predefined schema onto a table.

EnforceSchema.m

let
    EnforceTypes = (table as table, schema as table) as table =>
        let
            map = (t) => if t = type list or t = type record or t = type any then null else t,
            mapped = Table.TransformColumns(schema, {"Value", map}),
            omitted = Table.SelectRows(mapped, each [Value] <> null),
            transforms = Table.ToRows(omitted),
            changedType = Table.TransformColumnTypes(table, transforms)
        in
            changedType,

    EnforceSchema = (table as table, schema as table, optional trim as logical) as table =>
        let
            schemaNames = schema[Name],
            foundNames = Table.ColumnNames(table),
            addNames = List.RemoveItems(schemaNames, foundNames),
            extraNames = if trim = true then {} else List.RemoveItems(foundNames, schemaNames),
            tmp = Text.NewGuid(),
            added = Table.AddColumn(table, tmp, each []),
            expanded = Table.ExpandRecordColumn(added, tmp, addNames),
            result = if List.IsEmpty(addNames) then table else expanded,
            reordered = Table.SelectColumns(result, schemaNames & extraNames),
            changedType = EnforceTypes(reordered, schema)
        in
            changedType,

    EmptyTable = (schema as table) =>
        let
            schemaNames = schema[Name],
            table = Table.FromRows({}, schemaNames),
            changedType = EnforceTypes(table, schema)
        in
            changedType,

    ClassSchema = Record.ToTable([
        Id = Int32.Type,
        Name = type text,
        SubClass = type text,
        FullyQualifiedName = type text,
        Active = type any
    ]),

    SampleTable = Table.FromRows({{"Curt", "Hagenlocher", "Curt Hagenlocher", "Person"}}, {"Name", "LastName", "FullyQualifiedName", "SubClass"}),
    Enforced = EnforceSchema(SampleTable, ClassSchema, true)
in
    Enforced