How to use Excel UnPivot Columns for Fact Tables

Discover how to effectively use Excel Unpivot to transform your data entry tables into fact tables for better data analysis. This comprehensive guide provides step-by-step instructions, making the process of converting Excel tables using Unpivot Columns functionality easy and efficient.

If you have an Excel table built for data entry, it may not be in the best format for data analysis or your data model. Using Query Editor and the UnPivot Columns, you can keep the original table and create a version that you can use for analysis as a dimensional fact table.

Note: A Sample workbook is available on Github, WorkLifeBalance_Share_UnPivot.xlsx

The table pictured below is set up for easy data entry; however, it could be better for analysis. I want to treat the Expectations and Months as dimensions in queries. This creates more of a StarSchema type of fact table, built for analysis in such tools as Power BI.

Pictured below are the before and after. Before, each column is a month, making it easier for data entry. Below is a 3-column table that could be used in the analysis. Setting up the dates as columns allows for attaching a StarSchema Data Dimension table.

Note that the source range is set up as a table. You can use a range or a table, but setting up your “Tables” as “Tables” in Excel opens up many functionality. See the Post, 5 Steps to Improve Your Excel Data Tables, which includes how to set up a range as a table.

Step 1 – Select Table To Transform With Power Query

Using an easily forgotten set of functionality in Excel becomes easy to do. Select the top left column, in this example, the Expectations column.

As shown below, under the Data Menu, There is a section called Get & Transform Data. In that set of options, select From Table/Range. You will notice the popup message that you can use a named range, but it will be converted into a Table.

This brings up Power Query. This has many options to process your data for loading, but here we are just going to transform this range.

Step 2 – Select Range and UnPivot

As Pictured below, your selection is brought into the Power Query plugin. In 3 steps;

1. Select the Columns you want to transpose into 1 Column. Select the first column, scroll to the end, hold down shift, and select the last column. This will highlight your selection. If you have another sample, you would do this for each column you want to create.
2. On the top menu, select Transform
3. Select UnPivot Columns

Step 3 – Results

As shown below, you now have Power Query showing the results. If this is what you were looking for;

1. Double Click on the Month column heading and change the name from “Attribute” to Month.
2. Click on Close & Load to bring up a dialog

This adds a worksheet with the results to be used as a data source, leaving your original untouched.

Step 4 – New Table is Updateable With Refresh data

The main benefit of doing this is that it allows you to change values in the original table, and the changes flow through to your new table. The following steps are pictured below.

1. Change a value in the original table, Family Jan, to 500
2. Go to the New “UnPivot” Table
3. Select “Refresh” on the toolbar, and you will see the value is now 500 on the Family, Jan Cell.

Q&A

Q1 – What is the purpose of using the Unpivot Columns functionality in Excel?

The Unpivot Columns functionality in Excel is used to transform data that is set up for data entry into a format that is more suitable for data analysis. This is particularly useful when you want to convert your Excel tables into dimensional fact tables for analysis in tools like Power BI.

Q2 – How can I select the range to Unpivot in Excel?

To select the range to Unpivot, you need to first select the top left column of your table. Then, under the Data Menu, there is a section called ‘Get & Transform Data’. In that set of options, select ‘From Table/Range’. This will bring up Power Query, where you can process your data for loading.

Q3 – How do I use the Unpivot Columns function in Power Query?

Once your selection is brought into the Power Query plugin, follow these steps:

1. Select the columns you want to transpose into one column. Select the first column, scroll to the end, hold down shift, and select the last column. This will highlight your selection.
2. On the top menu, select ‘Transform’.
3. Select ‘Unpivot Columns’.

Q4 – How can I update the new table created using the Unpivot function?

The main benefit of using the Unpivot function is that it allows you to change values in the original table, and the changes flow through to your new table. To update the new table, simply change a value in the original table, go to the new ‘Unpivot’ table, and select ‘Refresh’ on the toolbar. The value in the new table will be updated accordingly.

Q5 – Can I use a named range instead of a table for the Unpivot function?

Yes, you can use a named range for the Unpivot function. However, it will be converted into a table in Power Query.

Conclusion

This quick tutorial shows how to take an Excel sheet used for data entry and convert it to a Fact Table by using the UnPivot Columns functionality in the Data Query tools in Excel.

Let me know what you think.   Does this fit into your workflow?  Let me know if you have any tricks you use.

Steve