April 17, 2018

What is the real-world use of Unpivoting columns?

244 Views

Preface

Unpivoting columns comes as part of the package when you are doing a tutorial on Power Query or Power BI. I’m sure, many (like me), do not really understand the use and go scampering searching for functions when it was the need to unpivot columns.

Use case or Scenario

Since we’ve already read volumes of what is done in unpivoting columns, let’s quickly jump to the scenario of what is given, moving to expectations to understand what is needed, and finally ending with the solution of how to do it.

Let’s say I’ve a tabulated data of a certain number of tasks assigned to a group of users, which is tabulated as below.

Task Name User 1 User 2 User 3 User 4
Task1 x x
Task2 x x
Task3 x x
Task4 x x
Task5 x x
Task6 x x

I like the row-column representation which makes readability easier, but when it comes to calculation, I find it easier working with data arranged in rows, with fewer columns to filter. If you still don’t understand what I intend to highlight, try doing the following calculations:

  1. Find how many persons performed any specific task, say, Task #2
  2. Find how many different tasks did any specific user perform, say User #1

It may not seem challenging now since the number of tasks and number of users performing those tasks is finite (read countable). However, with the increase in the user or the type of tasks, you’d wish doing it with the help of a function.

Expectation or Result

If I translated the same table into a slightly different structure, you’d exclaim with the same emotions as Archimedes used when he discovered Buoyancy - Eureka! After all, it’s just a matter of perspective.

Let’s see how we can do our calculations if the data was projected something like in the table below.

Task Name User
Task1 User 3
Task1 User 1
Task2 User1
Task2 User 4
Task3 User 2
Task3 User 3
Task4 User 1
Task4 User 4
Task5 User 2
Task5 User 4
Task6 User 1
Task6 User 3

Coming back to my favorite application Power BI, if I could succeed in transposing my data like the one above, I knew my calculations were just a click away.

I would easily split my visual data into two tables of the single column each so that they looked like the ones below

What is the real-world use of Unpivoting columns?

Needless to remind you of the prowess of visual representation of Power BI, the tables would be connected so that my visual filters would work like the one below

What is the real-world use of Unpivoting columns?

If this was not exactly what you were expecting when you thought I asked you found the exact count, you could further add a count to the visual.

On selecting the Task Name the names of Users performing the task would be filtered along with the display of count.

What is the real-world use of Unpivoting columns?

And on selecting the User, the linked Task Names along with the count would be updated.

What is the real-world use of Unpivoting columns?

Solution

On the Power BI Desktop window, go to Home > Edit Queries

What is the real-world use of Unpivoting columns?

On the Power Query Editor window, select the table, and then select the columns to unpivot and go to Home > Unpivot Columns

What is the real-world use of Unpivoting columns?

The table is transformed such that the column names corresponding to usernames become the Attribute and all the marked ‘X’ become the value.

What is the real-world use of Unpivoting columns?

Right-click on the column name Attribute and rename it to User for ease of comprehension.

What is the real-world use of Unpivoting columns?

Click on the column Value and do an ascending sort.

What is the real-world use of Unpivoting columns?

The next step is to remove the top blank rows from Home > Remove Top Rows.

What is the real-world use of Unpivoting columns?

What is the real-world use of Unpivoting columns?

Right-click on the Value column and Remove it.

What is the real-world use of Unpivoting columns?

As a final step (though not mandatory), perform ascending sort on Task Name to make the column ordered.

What is the real-world use of Unpivoting columns?

Advantage

Of the many advantages, I’ve also discovered that any semi-formatted or unformatted data if brought to the state where we first saw the row-column arrangement can easily be evaluated.

As an extended use case, I’ve also worked on values separated by special characters which I split into columns using delimiters before I could format the table into a row-wise data representation.

More to divulge in my next blog :)

Leave a Reply

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