Table.UnpivotOtherColumns

D

Back to top

T

Back to top

#

Back to top

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

What is Table.UnpivotOtherColumns?

Table.UnpivotOtherColumns is a Power Query M function that allows users to unpivot columns in a table while keeping other columns intact. This function is particularly useful when you have a table with multiple columns, and you want to unpivot some of them but leave others as they are.

For example, let’s say you have a table with the following columns:

| Name | Age | Address | City |

|——|—–|———|——|

| John | 25 | 123 Main St. | New York |

| Jane | 30 | 456 Elm St. | Los Angeles |

| Bob | 40 | 789 Oak St. | Chicago |

If you wanted to unpivot the “Age”, “Address”, and “City” columns, but leave the “Name” column as it is, you could use the Table.UnpivotOtherColumns function.

How to Use Table.UnpivotOtherColumns

To use the Table.UnpivotOtherColumns function, you need to have a table loaded into Power Query. Once you have your table loaded, follow these steps:

1. Select the columns you want to unpivot by clicking on their headers.

2. Right-click on one of the selected columns and choose “Unpivot Other Columns” from the context menu.

![Table.UnpivotOtherColumns](https://docs.microsoft.com/en-us/powerquery-m/table-unpivot-other-columns/Table.UnpivotOtherColumns.png)

3. Power Query will generate the M code for you, which you can view by clicking on the “Advanced Editor” button on the “View” tab.

![Advanced Editor](https://docs.microsoft.com/en-us/powerquery-m/advanced-editor/AdvancedEditorButton.png)

Understanding the M Code

Now that we know how to use Table.UnpivotOtherColumns, let’s take a closer look at the M code it generates.

Here’s an example of the M code generated by Table.UnpivotOtherColumns for our example table:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk3NzSjJzUwNzcxVrIwMwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Address = _t, City = _t]),

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value")

in

#"Unpivoted Other Columns"

Let’s break down what this code is doing:

– The “Source” line is creating a table from the rows of data that were loaded into Power Query.

– The “Unpivoted Other Columns” line is calling the Table.UnpivotOtherColumns function on the “Source” table. The first argument specifies which columns to leave as they are (in this case, “Name”), the second argument specifies the name of the new column that will contain the column headings that were unpivoted, and the third argument specifies the name of the new column that will contain the values that were unpivoted.

– The “in” line specifies the name of the output table (“Unpivoted Other Columns”).

Customizing Table.UnpivotOtherColumns

Table.UnpivotOtherColumns is a very flexible function that can be customized to fit almost any data set. Here are some ways you can customize this function:

– You can specify more than one column to leave as is by separating their names with commas in the first argument.

– You can specify a prefix for the new column headings by using the optional fourth argument.

– You can specify a custom delimiter for the new column headings by using the optional fifth argument.

Table.UnpivotOtherColumns is a powerful function in Power Query that allows users to unpivot columns in a table while leaving other columns untouched. By understanding how this function works and how to customize it, you can make the most of Power Query and transform your data 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)