I'm trying to use the Table.ChangeType helper from the TripPin sample for data connectors.
For some reason, when the column type is nullable record, it throws an error with the null values and I don't seem to understand why.
I'm using the code and JSON document below for testing (excuse the lazily done code).
[
{
"id": "1",
"fld": {
"name": "test"
}
},
{
"id": "2",
"fld": null
}
]
let
testType = type table [
id = text,
fld = nullable record
],
Table.ChangeType = 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])),
primativeTransforms = Table.ToRows(removeMissing),
changedPrimatives = Table.TransformColumnTypes(reordered, primativeTransforms),
// 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]), Function.Type),
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 changedPrimatives else Table.TransformColumns(changedPrimatives, 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 as type) as function =>
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,
Source = Json.Document(File.Contents("C:\Users\user\Desktop\test.json")),
tabl = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expand = Table.ExpandRecordColumn(tabl, "Column1", {"id", "fld"}, {"id", "fld"}),
testchangetype = Table.ChangeType(expand, testType)
in
testchangetype
