let // table should be an actual Table.Type, or a List.Type of Records Table.ChangeType = (table, tableType as type) as nullable table => // we only operate on table types if (not Type.Is(tableType, type table)) then error "type argument should be a table type" else // if we have a null value, just return it if (table = null) then table else let columnsForType = Type.RecordFields(Type.TableRow(tableType)), columnsAsTable = Record.ToTable(columnsForType), schema = Table.ExpandRecordColumn(columnsAsTable, "Value", {"Type"}, {"Type"}), previousMeta = Value.Metadata(tableType), // make sure we have a table parameterType = Value.Type(table), _table = if (Type.Is(parameterType, type table)) then table else if (Type.Is(parameterType, type list)) then let asTable = Table.FromList(table, Splitter.SplitByNothing(), {"Column1"}), firstValueType = Value.Type(Table.FirstValue(asTable, null)), result = // if the member is a record (as expected), then expand it. if (Type.Is(firstValueType, type record)) then Table.ExpandRecordColumn(asTable, "Column1", schema[Name]) else error Error.Record("Error.Parameter", "table argument is a list, but not a list of records", [ ValueType = firstValueType ]) in if (List.IsEmpty(table)) then #table({"a"}, {}) else result else error Error.Record("Error.Parameter", "table argument should be a table or list of records", [ValueType = parameterType]), reordered = Table.SelectColumns(_table, schema[Name], MissingField.UseNull), // process primitive values - this will call Table.TransformColumnTypes map = (t) => if Type.Is(t, type table) or Type.Is(t, type list) or Type.Is(t, type record) or t = type any then null else t, mapped = Table.TransformColumns(schema, {"Type", map}), omitted = Table.SelectRows(mapped, each [Type] <> null), existingColumns = Table.ColumnNames(reordered), removeMissing = Table.SelectRows(omitted, each List.Contains(existingColumns, [Name])), primitiveTransforms = Table.ToRows(removeMissing), changedPrimitives = Table.TransformColumnTypes(reordered, primitiveTransforms), // Get the list of transforms we'll use for Record types recordColumns = Table.SelectRows(schema, each Type.Is([Type], type record)), recordTypeTransformations = Table.AddColumn(recordColumns, "RecordTransformations", each (r) => Record.ChangeType(r, [Type]), type function), recordChanges = Table.ToRows(Table.SelectColumns(recordTypeTransformations, {"Name", "RecordTransformations"})), // Get the list of transforms we'll use for List types listColumns = Table.SelectRows(schema, each Type.Is([Type], type list)), listTransforms = Table.AddColumn(listColumns, "ListTransformations", each (t) => List.ChangeType(t, [Type]), Function.Type), listChanges = Table.ToRows(Table.SelectColumns(listTransforms, {"Name", "ListTransformations"})), // Get the list of transforms we'll use for Table types tableColumns = Table.SelectRows(schema, each Type.Is([Type], type table)), tableTransforms = Table.AddColumn(tableColumns, "TableTransformations", each (t) => @Table.ChangeType(t, [Type]), Function.Type), tableChanges = Table.ToRows(Table.SelectColumns(tableTransforms, {"Name", "TableTransformations"})), // Perform all of our transformations allColumnTransforms = recordChanges & listChanges & tableChanges, changedRecordTypes = if (List.IsEmpty(allColumnTransforms)) then changedPrimitives else Table.TransformColumns(changedPrimitives, allColumnTransforms, null, MissingField.Ignore), // set final type withType = Value.ReplaceType(changedRecordTypes, tableType) in if (List.IsEmpty(Record.FieldNames(columnsForType))) then table else withType meta previousMeta, // If given a generic record type (no predefined fields), the original record is returned Record.ChangeType = (record as record, recordType as type) => let // record field format is [ fieldName = [ Type = type, Optional = logical], ... ] fields = try Type.RecordFields(recordType) otherwise error "Record.ChangeType: failed to get record fields. Is this a record type?", fieldNames = Record.FieldNames(fields), fieldTable = Record.ToTable(fields), optionalFields = Table.SelectRows(fieldTable, each [Value][Optional])[Name], requiredFields = List.Difference(fieldNames, optionalFields), // make sure all required fields exist withRequired = Record.SelectFields(record, requiredFields, MissingField.UseNull), // append optional fields withOptional = withRequired & Record.SelectFields(record, optionalFields, MissingField.Ignore), // set types transforms = GetTransformsForType(recordType), withTypes = Record.TransformFields(withOptional, transforms, MissingField.Ignore), // order the same as the record type reorder = Record.ReorderFields(withTypes, fieldNames, MissingField.Ignore) in if (List.IsEmpty(fieldNames)) then record else reorder, List.ChangeType = (list as list, listType as type) => if (not Type.Is(listType, type list)) then error "type argument should be a list type" else let listItemType = Type.ListItem(listType), transform = GetTransformByType(listItemType), modifiedValues = List.Transform(list, transform), typed = Value.ReplaceType(modifiedValues, listType) in typed, // Returns a table type for the provided schema table Schema.ToTableType = (schema as table) as type => let toList = List.Transform(schema[Type], (t) => [Type=t, Optional=false]), toRecord = Record.FromList(toList, schema[Name]), toType = Type.ForRecord(toRecord, false), previousMeta = Value.Metadata(schema) in type table (toType) meta previousMeta, // Returns a list of transformations that can be passed to Table.TransformColumns, or Record.TransformFields // Format: {"Column", (f) => ...) .... ex: {"A", Number.From} GetTransformsForType = (_type as type) as list => let fieldsOrColumns = if (Type.Is(_type, type record)) then Type.RecordFields(_type) else if (Type.Is(_type, type table)) then Type.RecordFields(Type.TableRow(_type)) else error "GetTransformsForType: record or table type expected", toTable = Record.ToTable(fieldsOrColumns), transformColumn = Table.AddColumn(toTable, "Transform", each GetTransformByType([Value][Type]), Function.Type), transformMap = Table.ToRows(Table.SelectColumns(transformColumn, {"Name", "Transform"})) in transformMap, GetTransformByType = (type_in as type) as function => let _type = Type.NonNullable(type_in) in if (Type.Is(_type, type number)) then Number.From else if (Type.Is(_type, type text)) then Text.From else if (Type.Is(_type, type date)) then Date.From else if (Type.Is(_type, type datetime)) then DateTime.From else if (Type.Is(_type, type duration)) then Duration.From else if (Type.Is(_type, type datetimezone)) then DateTimeZone.From else if (Type.Is(_type, type logical)) then Logical.From else if (Type.Is(_type, type time)) then Time.From else if (Type.Is(_type, type record)) then (t) => if (t <> null) then @Record.ChangeType(t, _type) else t else if (Type.Is(_type, type table)) then (t) => if (t <> null) then @Table.ChangeType(t, _type) else t else if (Type.Is(_type, type list)) then (t) => if (t <> null) then @List.ChangeType(t, _type) else t else (t) => t in Table.ChangeType