Unpivot Columns And Keep Null Values in Power Query

Unpivoting in Power Query is a way to move columns into rows. It’s simple: just provide the columns you want to move, and the unpivot function does the rest

But by default, it removes any cells with a null value. In this article, you will learn how to keep null values when you unpivot in Power Query.

Table of contents

• Impact of Unpivot on Null Values
• Creating a Placeholder for Null Values
• Unpivoting and Preserving All Rows
• Restoring Null Values
• Conclusion

Impact of Unpivot on Null Values

Let’s say you have a table with the number of times each garment is sold in each month.

Notice how there are plenty of null values in the dataset. Since all values in this table represent the numbers sold of a particular garment, you may like to unpivot this table. The unpivot operation moves a list of columns, each into its own row. In that sense, it’s the opposite of the pivot operation.

You can unpivot using either Table.Unpivot or Table.UnpivotOtherColumns. The first unpivots only the selected columns, whereas the latter unpivots all the unselected columns. In this case, let’s unpivot other columns.

You can do that using either of the following methods:

• Right-click the Date Column -> Unpivot Other Columns.
• Go to the Transform tab -> Click the Unpivot Columns Dropdown -> Select Unpivot Other Columns.

You will end up with the below result. As you can see, the columns now each take in a row within the dataset.

So what is the problem?

The result of the unpivot operation is that each column becomes a row, but all the null values will be gone. This is the default behaviour of the unpivot function, and there’s no option to change it.

So what if you want to keep null values?

Creating a Placeholder for Null Values

To keep the null values, you need to use a workaround. The easiest way is to replace the nulls with a placeholder, such as 0 or a special character like ~ or |.

To do this, select the columns you want to change, right-click a column name and select “Replace Values…” This uses the Table.ReplaceValue function to replace the nulls with your placeholder.

Here’s the code produced to replace nulls with 0:

= Table.ReplaceValue(  
     #"Changed Type",
     null,
     0,
     Replacer.ReplaceValue, 
     {"Shirts", "Trousers", "Hats"} )

As you can see, the columns “Shirts”, “Trousers”, and “Hats” are now hardcoded.

Here’s a more dynamic way to do it, if you expect more columns in the future:

= Table.ColumnNames( #"Changed Type" ) // Returns all column names

// That includes the date. If you want to exclude the date column use: 
= List.RemoveItems( 
     Table.ColumnNames( #"Changed Type" ) , 
     {"Date"} )

// Then your formula becomes: 
= Table.ReplaceValue(  
     #"Changed Type",
     null,
     0,
     Replacer.ReplaceValue, 
     List.RemoveItems( 
          Table.ColumnNames( #"Changed Type" ) , 
          {"Date"} ) )

Unpivoting and Preserving All Rows

Now that the nulls have been replaced, you can unpivot the columns and keep all the rows.

In the resulting table, all the 0 values originally showed as null.

Restoring Null Values

To restore the nulls, select the “Value” column and replace the placeholder with null.

Note that for your scenario it may be meaningful to differentiate null from 0 values. In that case, perform the previous steps, but replace null by an uncommon character like ~ or |. It will work just the same.

Conclusion

Unpivoting in Power Query is a great way to change columns into rows. But by default, it removes null values. To keep the null values, you need to use a workaround like replacing the nulls with a placeholder. With this method, you can unpivot the columns and keep all the rows. And if you need to restore the nulls, simply replace the placeholder with null.

Happy querying!