Skip to content

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.

Source Data Entry Table
Source Data Entry Table

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.

Unpivot Other Columns
Unpivot Other Columns

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.

Transformed
Transformed

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

Close & Load
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.

Final Version
Final Version

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

No comments yet

Leave a Reply

%d bloggers like this: