Table.DuplicateColumn

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.DuplicateColumn

Understanding the Table.DuplicateColumn Function

The Table.DuplicateColumn function is a simple yet highly useful function in Power Query. It allows users to create a new column in a table that is a copy of an existing column, with a new name. This can be useful in a range of scenarios, such as when you want to apply a different set of transformations to the same data, or when you want to perform calculations on two different columns.

Here is an example of how to use the Table.DuplicateColumn function in Power Query:

let

Source = Table.FromRecords({

[Name="Alice", Age=25, City="London"],

[Name="Bob", Age=30, City="Paris"],

[Name="Charlie", Age=35, City="New York"]

}),

DuplicateColumn = Table.DuplicateColumn(Source, "Age", "AgeCopy")

in

DuplicateColumn

In this example, we start by creating a new table called “Source” with three rows of data. We then use the Table.DuplicateColumn function to create a new column called “AgeCopy”, which is a copy of the “Age” column in the “Source” table.

The M Code Behind the Table.DuplicateColumn Function

To better understand how the Table.DuplicateColumn function works, it’s important to take a look at the M code behind it. The M code is the language used by Power Query to perform data transformations and create queries.

Here is an example of the M code behind the Table.DuplicateColumn function:

(Table as table, ColumnName as text, NewColumnName as text) as table =>

let

ColumnIndex = Table.ColumnIndex(Table, ColumnName),

NewType = Value.Type(Table{0}[ColumnName]),

Inserted = Table.AddColumn(Table, NewColumnName, each Table.Column(Table, ColumnIndex), NewType)

in

Inserted

In this code, we can see that the Table.DuplicateColumn function takes three arguments: the original table, the name of the column to duplicate, and the name of the new column. The function then uses the Table.ColumnIndex function to find the index of the column to duplicate, and the Value.Type function to get the data type of the column.

Finally, the function uses the Table.AddColumn function to add a new column to the original table. The new column is named using the “NewColumnName” argument, and its values are set using the Table.Column function. The “NewType” argument is used to ensure that the data type of the new column matches that of the original column.

Key Features of the Table.DuplicateColumn Function

The Table.DuplicateColumn function has a range of key features that make it a highly useful tool for data transformation and cleansing. Here are some of the key features of this function:

1. Customizable column names

One of the key features of the Table.DuplicateColumn function is the ability to customize the name of the new column. This can be useful when you want to keep track of multiple copies of the same data, or when you want to apply different transformations to different copies of the same data.

2. Retains original data types

Another key feature of the Table.DuplicateColumn function is that it retains the original data type of the column being duplicated. This ensures that data is not lost or altered during the transformation process, and that subsequent calculations and transformations can be performed without issues.

3. Allows for easy data comparisons

By creating a copy of an existing column, the Table.DuplicateColumn function allows users to easily compare and analyze data without altering the original data set. This can be useful in a range of scenarios, such as when you want to compare the results of different calculations or transformations on the same data.

In conclusion, the Table.DuplicateColumn function is a highly useful tool in Power Query that allows users to create a copy of an existing column in a table with a new name. This function is simple to use, yet highly customizable and flexible, making it a valuable tool for data analysts and other professionals in the data industry. By understanding the M code behind this function and its key features, users can make the most of the power and flexibility of Power Query and create efficient and effective data transformations and cleansing workflows.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)