-
Notifications
You must be signed in to change notification settings - Fork 2
/
Table.ChangeType.pqm
193 lines (192 loc) · 9.87 KB
/
Table.ChangeType.pqm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
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]), 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
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