Value.ReplaceType Function in Power Query M (6 Easy Examples)

Value.ReplaceType returns a copy of a value with its type replaced by a type you provide. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to attach a richer type to a value, like giving a table column descriptions or an explicit data type, this is the function you reach for. It changes the type label only, so the underlying data stays exactly as it was.

Table of Contents

Syntax of Value.ReplaceType Function

Value.ReplaceType(value as any, type as type) as any

where

value (required, any). The value whose type you want to replace. It can be a scalar, a record, a table, or a function.
type (required, type). The new type to attach to value. For a table this is a type table [...] that lists the columns.

Returns: a copy of value with the new type applied. The data is never altered, only the type metadata.

In plain terms, you hand it a value and a type, and it stamps that type onto the value without checking or converting the data behind it.

Example 1: Ascribe a type to a single value

Take the number 123 and tell Power Query its type is type number, then confirm it.

let
    Source = Value.ReplaceType(123,type number),
    Result = Value.Is(Source,type number)
in
    Result

Result: true

Value.Is confirms the new type took hold, so the ascribed type number is now attached to the value.

Example 2: Ascribe a field type to a record

Take a record [Country="Kenya"] and ascribe a record type that marks Country as text, then read the field type back.

let
    Typed = Value.ReplaceType([Country="Kenya"],type [Country=text]),
    Result = Type.RecordFields(Value.Type(Typed))[Country][Type]
in
    Result

Result: type text

Reading the field type back returns a type value (type text here), which is how you confirm the ascribed record type.

Example 3: Rename and read a table’s column names

Say you have a ProjectLog table. You want to attach an explicit table type to it, then read the column names back out.

Here is the starting data:

Phase	Owner
Discovery	Maya
Build	Raj
Launch	Tom

Ascribe a type table and pull the column names from its schema:

let
    Source = Excel.CurrentWorkbook(){[Name="ProjectLog"]}[Content],
    NewType = type table [Phase=text,Owner=text],
    Typed = Value.ReplaceType(Source,NewType),
    Result = Type.TableSchema(Value.Type(Typed))[Name]
in
    Result

Type.TableSchema reads the ascribed type and returns one row per column.

The result lists the column names:

Name
Phase
Owner

The names come straight from the type you ascribed, not from the source headers.

Example 4: Ascribe explicit column data types

This time you want each column of an OrderBook table to carry a specific data type, then you want to read those type names back.

Here is the starting data:

Ref	Units
REF-101	4
REF-102	9
REF-103	2

Ascribe text and Int64.Type to the two columns, then read the schema’s type names:

let
    Source = Excel.CurrentWorkbook(){[Name="OrderBook"]}[Content],
    NewType = type table [Ref=text,Units=Int64.Type],
    Typed = Value.ReplaceType(Source,NewType),
    Schema = Type.TableSchema(Value.Type(Typed)),
    Result = Schema[TypeName]
in
    Result

Each column now reports the type you stamped onto it.

The result lists the type names:

TypeName
Text.Type
Int64.Type

Ref reads back as Text.Type and Units as Int64.Type, matching the type you ascribed.

Example 5: Attach column descriptions to a table

Here is the real payoff. You want each column of a StaffList table to carry a description that Power Query can surface, then read those descriptions back.

Here is the starting data:

EmployeeId	Team
EMP-7	Finance
EMP-8	Finance
EMP-9	Ops

Ascribe a table type whose columns carry Documentation.FieldDescription metadata:

let
    Source = Excel.CurrentWorkbook(){[Name="StaffList"]}[Content],
    NewType = type table [
        EmployeeId = (type text meta [Documentation.FieldDescription="Unique staff code"]),
        Team = (type text meta [Documentation.FieldDescription="Department the person works in"])
    ],
    Typed = Value.ReplaceType(Source,NewType),
    Result = Type.TableSchema(Value.Type(Typed))[Description]
in
    Result

The meta record on each column type holds the description, which the schema reads back.

The result lists the descriptions:

Description
Unique staff code
Department the person works in

Attaching documentation metadata to a table’s columns is the most common reason to reach for this function, since the descriptions then travel with the type.

Example 6: Confirm the data is left untouched

To prove that ascribing a type changes nothing about the rows, take an InvoiceData table and count its rows before and after.

Here is the starting data:

InvoiceNo	Amount
INV-A	150
INV-B	240
INV-C	60

Ascribe column types, then count the rows:

let
    Source = Excel.CurrentWorkbook(){[Name="InvoiceData"]}[Content],
    NewType = type table [InvoiceNo=text,Amount=Int64.Type],
    Typed = Value.ReplaceType(Source,NewType),
    Result = Table.RowCount(Typed)
in
    Result

Result: 3

The row count is unchanged, confirming Value.ReplaceType swaps the type metadata only and never rewrites the data.

Things to keep in mind with Value.ReplaceType

It ascribes, it does not convert. The data is never inspected or changed. If you stamp type number onto text, the label says number while the value stays text, which can break a later step. Use Table.TransformColumnTypes when you actually want to convert.
A table type must declare the SAME number of columns as the value. A mismatched column count throws Expression.Error: We cannot convert the specified value to the specified type. List every column of the value in the replacement type.
Column types read back with their canonical name. A column ascribed as text reports Text.Type and Int64.Type stays Int64.Type in Type.TableSchema (Example 4), not the short form you typed.
Metadata only sticks if you read it from the type, not the table. Column descriptions and other meta live on the ascribed type, so reach them through Value.Type and Type.TableSchema, as in Example 5.
It only relabels, like the columnType in Table.AddColumn. Declaring a type there does not convert the values either, so a generator that returns text stays text under a number label.
It is the standard way to document a custom function. Ascribe a type function (...) as ... meta [Documentation.Name=...] to a function value so Power Query renders a proper invocation UI.

Common questions about Value.ReplaceType

What is the difference between Value.ReplaceType and Table.TransformColumnTypes?

Table.TransformColumnTypes actually converts each value to the new type and errors on data that will not convert. Value.ReplaceType only relabels the type and never touches the data, so it is faster but offers no safety check.

Can I use it to add column descriptions that show in the field list?

Yes. Ascribe a table type whose columns carry meta [Documentation.FieldDescription=...], as in Example 5. The descriptions travel with the type metadata.

List of All Power Query Functions

Related Power Query Functions / Articles: