The Unpivot Paradox - What works and what does not ? How a string can make your life difficult ?

[monaraya]

Disclaimer: All data samples in this blog have random values. Should these values match with some other blog/article, it is something purely coincidental.

In layman terms, Power BI as we know is a very user-friendly (emphasis on very) data visualization and analysis tool.If I am a beginner, I need to click a few buttons and I can create a very sophisticated, structured report.

However, sometimes hard comes with easy. While mostly I get what I want but one wrong click - In Power BI terms a step - can lead to what I call a panic web.

I recently came across one pretty simple but easy to miss scenario. Customer was trying to unpivot a few selected columns to get what he wanted but got an empty table instead. If I may say, he was definitely in a panic web.

Now before we get into details, let us first understand what pivot and unpivot means.

Pivot Columns

Pivot is used to create a name value column out of 2 separate columns. For example, you are a Project Manager and need to see the budget released for various cycles for your project each month. You have data collated in your source (here Excel) something of this form.

Not really readable and insightful right?

Doesn't this look better?

If I were to make such changes manually, it would take me approximately 30 minutes. And imagine I have 100 such projects.

Pivot will help you achieve this in a few clicks. Not getting into details of how.I hope this helped you understand how helpful this can be.

Unpivot Columns

Unpivot, as the name suggests, does the opposite of Pivot. It basically changes multiple column headers into a single column but in rows and stores their values in another column.

So now if you want to go from this:

To this:

Unpivot is your guy.

For more on Pivot and Unpivot and how to, refer to

Pivot columns - Power Query | Microsoft Docs

Unpivot columns - Power Query | Microsoft Docs

Now that we have a fair understanding of Pivot and Unpivot. Let's move towards the solution.

The Unpivot Paradox

As we may know, null values passed in the input of UNPIVOT columns are removed from the dataset. What does this really mean?

Imagine you have data looking like this.

Now I select Column 2, Column 3, Column 4 and perform 'Unpivot Only Selected Columns' and this is what I get.

Mine (and probably yours too) first reaction would be 'Hey. Why did this happen?'

This is because rows with null values passed in the input of UNPIVOT columns are removed from the dataset.

A common question here - Why would UNPIVOT do that?

Answer - It cannot really separate null into Attribute/Name and Value.

What can we do for such columns and we still need to unpivot? Change null to 0 --> Unpivot --> Change 0 back to null.

Data after replacing null with 0.

Data after unpivot.

Data after replacing 0 with null.

Now what if I have something like this.

Now I select Column 2, Column 3, Column 4 and perform 'Unpivot Only Selected Columns' .This is what I get.

Did we not understand a moment ago that null values are removed from the dataset? What are my nulls (short for null values) still doing here?

In all programming languages (all those I know of) or query languages, a reserved keyword and a string are never the same. For example True is never same as "True".

Here's a small Python program to illustrate this.

Output:

Since null was entered as a value in the source, it was taken as a text or a string instead of a reserved keyword because of which UNPIVOT did not return empty values.

Question - How to differentiate if null is the keyword null or text/string null?

Answer-Reserved Keyword will come in Italics, something like this.

While text/string null would come like any other text.

Conclusion

• Rows with null values will be removed from the dataset after Unpivot.
• Null entered in the source can be treated as string and such rows will not be removed after Unpivot.
• In case an unpivot is needed on columns containing null values, replace null with 0 then unpivot. Replace 0 with null after unpivot.

I hope after reading this if you ever run into an empty table after Unpivot, you won't go into a panic web and you will check if you are unpivoting null columns. 😊

Author: @aksriva Akshiita Srivastava
Reviewer: Mounika Narayana Reddy