Record.TransformFields
Returns a copy of a record in which chosen fields have been changed by a function.
Record.TransformFields(record, transformOperations, optional missingField)Returns: A new record with the transformed values.
What it does
Record.TransformFields applies a function to chosen fields of a record and leaves the rest alone. Use it to convert text amounts to numbers, trim spaces, or round a value, one field at a time. It is the record version of changing a few columns in a table.
Syntax and arguments
record: the record to change.transformOperations: a list of pairs, each with a field name and the function to apply, such as{{"Amount", Number.From}}.missingField(optional): what to do if a named field is absent. The default is an error.
Example
A row from a text export has the amount and the date stored as text. The row needs proper types before it is totalled.
let
RawRow = [Date = "2026-03-31", Amount = "1,250.00", Memo = "Rent"],
Cleaned = Record.TransformFields(RawRow, {
{"Amount", each Number.FromText(Text.Replace(_, ",", ""))},
{"Date", each Date.FromText(_)}
})
in
Cleaned
Cleaned has the date as a date value and the amount as the number 1250. The memo is left as it was.
Example: a second, different use
Rounding one field to cents while leaving the others alone: {{"Rate", each Number.Round(_, 4)}} rounds only that field.
Things to watch
- The function is applied to the named field only. Other fields are copied as they are.
- Conversion errors stop the query. Wrap risky conversions in
try ... otherwiseto give a fallback. - Number.From is a quick conversion, but it depends on the locale of the PC. Use Number.FromText with a culture when the decimal sign matters.
- A missing field raises an error unless you pass
MissingField.Ignore.
In business terms
Converting text exports to numbers and dates before they go into a model is a daily task in finance data work. Showing each conversion in the query makes the cleanup visible for review.