Power Query Mash-up Function: Unpivot Columns using Table.Unpivot

Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn how to unpivot columns in Power Query using mash-up function

Table.Unpivot M Function translates a set of columns in a table into attribute-value pairs combined with the rest of the values in each row

Table.Unpivot Syntax                     =Table.Unpivot( table, pivotColumns, attributeColumn, valueColumn)

We want to unpivot the columns of the data in the caption below using Table.Unpivot Power Query M Function

To do that: In the General group of the Add Column tab, click on Custom Column

In the Custom Column dialogue box, execute the formula in the custom column formula:

Table.UnpivotOtherColumns(Source, {“Acct Managers”}, “Quarters”, “Total”)

Click OK

The next thing we want to do is remove all the columns except the custom columns.

To do that, select Acct Managers column, hold the SHIFT key and click on Qtr4 column

Right-click and select Remove Columns

Next, click on the double-upward expandable arrows

If the Use original column name as prefix is automatically checked, uncheck the box.

Click OK.

We have successfully unpivot the columns using Table.Unpivot Mash-up Function.

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *