How to Convert Excel Tables into Dimensional Fact Tables Using Query Editor
If you have an excel table that is built for data entry, it may not be in the best format for data analysis or your data model. By using Query Editor, 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, could be in a better format for analysis. What I want to do is 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 in this case 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. 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 end 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.