Downloads
Power Query MTable: columnsBeginner

Table.DuplicateColumn

Copies a column into a new column with a name you choose.

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

Returns: The table with an extra copy of the column.

What it does

Table.DuplicateColumn makes a copy of a column so you can keep the original values while you change the working copy. The copy is independent. Changing the original later does not change the copy. A common use is keeping the raw text of an amount, so a reviewer can see exactly what the bank file said.

Syntax and arguments

Example

A bank export has an Amount column with text such as "1,250.00". Before the amount is converted to a number, a copy of the raw text is kept for the reviewer.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    KeepRaw = Table.DuplicateColumn(Source, "Amount", "Amount as exported", type text),
    Typed = Table.TransformColumnTypes(KeepRaw, {{"Amount", type number}})
in
    Typed

The Amount column becomes a number. The Amount as exported column still holds the original text, so the reviewer can compare the two when a value looks wrong.

Example: a second, different use

A transaction date is copied before it is changed to a month start, so the original date stays in the table for the detail view.

let
    Source = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
    WithPeriod = Table.DuplicateColumn(Source, "PostingDate", "Period"),
    Monthly = Table.TransformColumns(WithPeriod, {{"Period", Date.StartOfMonth, type date}})
in
    Monthly

Things to watch

In business terms

Keeping the source value next to the cleaned value is a simple audit control. It lets the preparer show the reviewer exactly how each figure was converted.

Related functions

All Power Query M functions · View as Markdown · M basics