Table.RemoveColumns

D

Back to top

T

Back to top

#

Back to top

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

Introduction to Table.RemoveColumns Function

The Table.RemoveColumns function is used to remove a specified list of columns from a table in Power Query. The syntax for this function is as follows:

Table.RemoveColumns(table as table, columnNames as list) as table

Here, `table` refers to the input table from which the columns need to be removed, and `columnNames` is a list of column names that need to be removed. The output of this function is a new table with all the specified columns removed.

Understanding the M Code Behind Table.RemoveColumns Function

The Power Query uses a functional language called M to perform data transformations. The Table.RemoveColumns function is a built-in M function that can be customized based on user requirements. Let’s take a look at the M code behind the Table.RemoveColumns function.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlQyUFJSgjJwNDYyUDM2LzS2MDEwVayCUB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),

RemovedColumns = Table.RemoveColumns(Source,{"Column1", "Column2"})

in

RemovedColumns

Here, the M code first creates a source table from a compressed binary value. The source table contains three columns named Column1, Column2, and Column3. Then, the Table.RemoveColumns function is used to remove Column1 and Column2 from the source table, and the result is stored in a new variable named `RemovedColumns`.

Customizing the Table.RemoveColumns Function

The Table.RemoveColumns function can be customized based on user requirements. For instance, if we want to remove all the columns except a few specified columns, we can modify the M code as follows:

let

Source = #table({"Column1", "Column2", "Column3", "Column4", "Column5"}, {{1, 2, 3, 4, 5}, {6, 7, 8, 9, 10}}),

ColumnNames = List.RemoveItems(Table.ColumnNames(Source), {"Column1", "Column2"}),

RemovedColumns = Table.RemoveColumns(Source, ColumnNames)

in

RemovedColumns

Here, we have created a source table with five columns and two rows. We want to remove all the columns except Column1 and Column2. To achieve this, we have used the List.RemoveItems function to create a new list of column names that need to be removed. This list is then passed to the Table.RemoveColumns function to create a new table with only Column1 and Column2.

In conclusion, the Table.RemoveColumns function is a powerful tool in the Power Query that helps users to remove unwanted columns from a table. The M code behind this function can be customized based on user requirements, making it a versatile tool for data transformation. By understanding the M code behind the Table.RemoveColumns function, users can gain better control over their data and perform complex data transformations with ease.

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)