Table.DuplicateColumn Function in Power Query M (3 Examples)

If you want to copy an existing column into a brand new column, Table.DuplicateColumn does it in one step. It is handy when you need to transform a column but keep the original intact.

Table of Contents

Syntax of Table.DuplicateColumn Function

Table.DuplicateColumn(table as table, columnName as text, newColumnName as text, optional columnType as nullable type) as table

where

table (required, table). The source table you want to copy a column from.
columnName (required, text). The name of the existing column to copy.
newColumnName (required, text). The name for the new column that gets added.
columnType (optional, nullable type). Ascribes a type to the new column. It does not convert the values, it only labels the type.

Returns: a table with a copy of columnName added as newColumnName at the end.

So you point at a column, give the copy a name, and Table.DuplicateColumn adds an identical column at the far right of the table.

Example 1 – Make a Basic Copy of a Column

Say you have a contacts table and you want a second copy of the Email column to work with.

Here is the starting data:

Name	Email
Asha Menon	asha@acme.io
Ravi Iyer	ravi@globex.com
Lena Park	lena@initech.co

let
    Source = Excel.CurrentWorkbook(){[Name="Contacts"]}[Content],
    Dup = Table.DuplicateColumn(Source,"Email","EmailCopy")
in
    Dup

This gives the following result:

Name	Email	EmailCopy
Asha Menon	asha@acme.io	asha@acme.io
Ravi Iyer	ravi@globex.com	ravi@globex.com
Lena Park	lena@initech.co	lena@initech.co

The new EmailCopy column is an exact copy of Email and lands at the end of the table.

Example 2 – Duplicate a Column, Then Transform the Copy

This is the main reason to use the function. You want to extract the domain from each email but still keep the full address. So you duplicate Email first, then transform the copy.

Here is the starting data:

Name	Email
Asha Menon	asha@acme.io
Ravi Iyer	ravi@globex.com
Lena Park	lena@initech.co

let
    Source = Excel.CurrentWorkbook(){[Name="Emails"]}[Content],
    Dup = Table.DuplicateColumn(Source,"Email","Domain"),
    GetDomain = Table.TransformColumns(Dup,{{"Domain",each Text.AfterDelimiter(_,"@"),type text}})
in
    GetDomain

This gives the following result:

Name	Email	Domain
Asha Menon	asha@acme.io	acme.io
Ravi Iyer	ravi@globex.com	globex.com
Lena Park	lena@initech.co	initech.co

The original Email column stays untouched while the duplicated Domain column holds only the part after the @.

Example 3 – Set the Type of the New Column

Here you copy Price into PriceWithTax, ascribe type number to the copy, then add 10% tax to it.

Here is the starting data:

Product	Price
Mouse	25
Keyboard	45
Monitor	180

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    SetType = Table.TransformColumnTypes(Source,{{"Price",type number}}),
    Dup = Table.DuplicateColumn(SetType,"Price","PriceWithTax",type number),
    AddTax = Table.TransformColumns(Dup,{{"PriceWithTax",each _*1.1,type number}})
in
    AddTax

This gives the following result:

Product	Price	PriceWithTax
Mouse	25	27.5
Keyboard	45	49.5
Monitor	180	198

The columnType argument labels PriceWithTax as type number so the later math runs cleanly on the copy.

Things to Keep in Mind with Table.DuplicateColumn

The new column is always appended at the far right of the table. If you want it next to the original, follow up with Table.ReorderColumns.
It errors if newColumnName already exists. There is no overwrite, so pick a fresh name.
It is a straight copy, not a calculation. If you need a value derived from a formula, use Table.AddColumn instead.
The optional columnType only ascribes a type. It does not convert the values, so forcing type text onto numeric data gives type errors, not text. To actually change values, run Table.TransformColumnTypes afterward.
Without columnType, the copy inherits the source column’s type. If the source was never typed, the copy comes through as any, so set the type first if you plan to do math on it.

Common Questions About Table.DuplicateColumn

When should I use Table.DuplicateColumn instead of Table.AddColumn?

Use Table.DuplicateColumn when you just want an exact copy of an existing column. Reach for Table.AddColumn when the new column’s value comes from a formula or expression you write.

How do I put the duplicate right next to the original?

The copy always lands at the end of the table. Add a Table.ReorderColumns step after the duplicate to move the new column wherever you want it.

List of All Power Query Functions

Related Power Query Functions / Articles: