Table.DuplicateColumn

Table.DuplicateColumn is a Power Query M function that duplicates a specified column in a table, copying the values and type from the original column. The function returns a new table with the duplicated column.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
table		The table containing the column to duplicate.
columnName		The name of the column to duplicate.
newColumnName		The name of the duplicated column.
columnType	optional	The data type to ascribe to the new column.

Description

Table.DuplicateColumn duplicates an existing column in a table. It creates a new column with the same values as the original and by default retains the data type of the original column. You can optionally specify a different data type for the duplicated column.

Examples

Let’s walk through a few examples to see how the Table.DuplicateColumn function works.

Suppose you’re working with the following table:

You want to create a copy of the DurationMinutes column to apply separate transformations without altering the original data. To duplicate the column, you can either use the Power Query interface by right-clicking on the column and selecting Duplicate Column or write the following code:

Table.DuplicateColumn(
  Source,                   // the table containing the column
  "DurationMinutes",        // the column to duplicate
  "DurationMinutes - Copy"  // the name of the new column
)

This adds a new column to the table with the name "DurationMinutes - Copy":

Ascribing a Data Type

By default, the new column retains the same data type as the original. However, you can change the data type by using the optional fourth argument of Table.DuplicateColumn.

For example, to duplicate the DurationMinutes column but change its data type to text:

Table.DuplicateColumn(
  Source, 
  "DurationMinutes", 
  "DurationMinutes - Copy", 
  type text 
)

The outcome of this operation is a column with type text ascribed as data type:

If you try to load this into a destination like Power BI, you may encounter errors if the data contains values incompatible with the new type. For instance, the original column’s numeric values will now be treated as text strings.

Try It Yourself

You can test these examples by pasting the following code into Power Query’s Advanced Editor:

let
  Source = 
    #table(
      type table [ StepName = text, TaskDescription = text, DurationMinutes = Int64.Type ],
      {
          { "Initialization",   "Set up the project environment", 10 },
          { "Data Collection",  "Gather necessary data",          30 },
          { "Data Cleaning",    "Clean and preprocess data",      25 },
          { "Modeling",         "Build the data model",           40 },
          { "Validation",       "Validate the model",             20 },
          { "Deployment",       "Deploy the model",               15 },
          { "Monitoring",       "Monitor deployed model",         15 }
      }
    ),
    DuplicateColumn = Table.DuplicateColumn(Source, "DurationMinutes", "DurationMinutes - Copy" ),
    AscribeType = Table.DuplicateColumn( Source, "DurationMinutes", "DurationMinutes - Copy", type text )
in
    AscribeType

Related functions

Other functions related to Table.DuplicateColumn are:

• Table.AddColumn
• Table.AddIndexColumn
• Table.AddRankColumn

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-duplicatecolumn