Table.DuplicateColumn
Copies a column into a new column with a name you choose.
Table.DuplicateColumn(table as table, columnToDuplicate as text, newColumnName as text, optional columnType as nullable type) as tableReturns: 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
- table: the table that holds the column. Required.
- columnToDuplicate: the name of the column to copy, in quotes. Required.
- newColumnName: the name for the copy, in quotes. The name must not already be used. Required.
- columnType: the type of the copy. Optional.
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
- The copy holds the values at the time of the step. Later changes to the original do not reach it.
- The new column name must be unique. A name that already exists is an error.
- Keeping a raw copy costs memory, so do it only for columns you need to audit.
- Query folding: duplicating a column usually folds, but check View Native Query if the source is a database.
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.