How to Convert Excel Tables Fact Tables Using Query Editor​

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.

The table pictured below is set up for easy data entry; however, it could be better for analysis. I want to treat the Week Number, Scenario, and Classification as dimensions in queries. I also want to combine the Week Number with the fiscal dates to create a DateTime dimension.

Using a little-known set of functionality in Excel, this becomes easy to do. With the top cell highlighted in the table, Scenario as the top row is just a title row. Under Data on the toolbar, then select the From Table option. The Query Editor Screen comes up.

Highlight the columns that you want to transpose, in this case, Version and Classification. In the Unpivot Columns options drop-down, select Unpivot Other Columns. This will transform into the version below.

One final edit to change the Attribute column name to WeekNum, and then go back to the Home tab and select Close & Load.

The result below is an updating table in a better format to be used in a data model. The Worksheet tab has been renamed to Fact Table in this example.

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