Power BI MCSA Certification Tips – Part 4: Unpivot Columns and Unpivot Other Columns

To obtain your MCSA: BI Reporting you need to pass two exams: 70-778 (Analyzing and Visualizing Data with Power BI) and 70-779 (Analyzing and Visualizing Data with Excel). However, there is a huge overlap between the two exams resulting from the common BI features found in both Power BI and Excel: namely data modelling using the DAX language; and data connection and transformation using the M language.

We, at G Com Solutions, recommend to the clients to whom we supply Power BI Training that they prepare for both exams simultaneously and then sit both exams on the same day. We also run a 5 day Power BI MCSA Certification Prep course in London which includes both exams 70-778 and 70-779.

In this series of blog posts, we will be examining some of the key features of Power BI, Power Pivot and Power Query which you will need to master in order to pass the two exams.

In this topic, which applies equally to both Power BI and Excel Power Query, we will examine two related M functions: Table.UnpivotColumns and Table.UnpivotOtherColumns.

Table.UnpivotColumns

Power Query allows you to connect to lots of different data sources; and it is inevitable that, from time to time, the data source to which you are connecting is going to be a report generated by another system.

One of the attributes that reports sometimes contain is pivoting, which is where the members of a category have been separated out into columns. The following illustration shows an example using Excel data.

Here is the source data.

<![if !vml]><![endif]>

And, here is a pivot Table based on that source data.

<![if !vml]><![endif]>

As you can see we have Category arranged in rows; we have Month arranged in columns and the value which we are analysing is Amount Reimbursed.

Ideally, we would want to import the raw data into Power BI, since this will give us the maximum amount of flexibility. In the case of Excel, pivoting is often easy to avoid, or to reverse; and I should also point out that you cannot import data directly from an Excel pivot table into Power BI. However, many systems also output reports in this same pivoted format; and it is here that you will encounter situations where unpivoting is required.

The problematic aspect, in the above illustration, is the fact that months are separated out into columns, or, pivoted. Whenever you connect to a report with this feature, Power BI’s Unpivot command will reverse the pivoting and reduce the separate columns down to two columns: one, containing a description; and, the other, a value. The entries which were headings in the report will become entries within the description column and as many extra rows as necessary will be generated.

In earlier versions of Power BI, there were two ways of proceeding: either select the columns you want to unpivot then choose Transform > Any Column > Unpivot Columns; or, if the columns you wish to unpivot are in the minority, it may be quicker to select the other columns then choose Transform > Any Column > Unpivot Other Columns. Although these two actions can still be performed, they will now yield an identical result, in terms of the M syntax generated: the function used will always be Table.UnpivotOtherColumns. This is a useful default; since, when connecting to pivoted data generated by another system, the names of the columns to be unpivoted will often be different each time, particularly if they are date-sensitive.

Thus, in the illustration below, “Restaurant” and “Date” are the only two columns which we do not want to unpivot, so we highlight these two columns and use Unpivot Other Columns. However, if we chose to highlight all the columns we want to unpivot and use the Unpivot Columns command, we would still be generating a Table.UnpivotOtherColumns command.

<![if !vml]><![endif]>

Unpivot only Selected Columns

If you want to explicitly generate the Table.UnpivotColumns command, you can do so by using the third command which is available in the Unpivot Columns drop-down: Unpivot only Selected Columns. Generating the Table.UnpivotColumns syntax is the main purpose of this command and the only thing which distinguishes it from the other two options.

<![if !vml]><![endif]>

Attributes and Values

When you unpivot columns, you always end up with two columns, named “Attributes” and “Values”. The “Attribute” column contains the original column headings, while the “Values” column contains the entries which were underneath each of those headings. The Query Editor automatically marries everything up for you, creating as many extra rows as necessary. You will then have a much more flexible dataset for building reports.

Syntax

Table.Unpivot(

table as table,

pivotColumns as list,

attributeColumn as text,

valueColumn as text

) as table

<![if !supportLists]>·        <![endif]>Table
The table to be transformed.

<![if !supportLists]>·        <![endif]>PivotColumns
A list of the columns to be unpivoted.

<![if !supportLists]>·        <![endif]>AttributeColumn
An optional name to replace the default name for the attribute column.

<![if !supportLists]>·        <![endif]>ValueColumn
An optional name to replace the default name for the Value column.

UnpivotOtherColumns

Whenever Table.UnpivotColumns is used, if one of the pivot columns is subsequently renamed, an error will be produced. However, if Table. UnpivotOtherColumns is used, there is no reference to the columns being pivoted; and, thus, no error will be produced if one of the pivoted columns is renamed. Since UnpivotOtherColumns provides more flexibility, it is now used in preference to UnpivotColumns, regardless of the action used in the Power Query Editors.

Syntax

Table.UnpivotOtherColumns(

table as table,

pivotColumns as list,

attributeColumn as text,

valueColumn as text

) as table

<![if !supportLists]>·        <![endif]>Table
The table to be transformed.

<![if !supportLists]>·        <![endif]>PivotColumns
A list of the columns which will not be unpivoted.

<![if !supportLists]>·        <![endif]>AttributeColumn
An optional name to replace the default name for the attribute column.

<![if !supportLists]>·        <![endif]>ValueColumn
An optional name to replace the default name for the Value column.