Excel provides you with certain features for creating Data Models. You may also need to add a table to the data model. Hold on if you are on this way! This tutorial will provide you with a complete guideline to add table to data model in Excel.
How to Add Table to Data Model in Excel: 3 Effective Ways
Let’s say, we have a dataset consisting of 2 tables with some information about some sellers of a company. One is about the name and ID of the Sellers and another describes the ID, Products, and amount of Sales they have made.
In this section, you will find 3 effective ways to add table to data model in Excel. I will demonstrate them one by one here. Let’s check them now!
1. Applying Relationship Feature
Excel has a unique feature for creating relationships and adding tables between two datasets => Relationship. Applying this feature, you can add table to the data model.
- First of all, click a random cell of your first dataset.
- Then, go to the Insert tab> click Table under the Tables group.
- Then, a dialog box with the heading Create Table will appear in the window. In the data field of the box, you will see that Excel has recognized all the cells of the range of the data although you have selected only a single cell. Keep the My table has headers marked and then click OK.
- Hence, a table will be inserted into your data range.
- Now, select a random cell in the table and a new tab named Table Design in the toolbar.
- Go to the Table Design tab> assign a name to your table (i.e. Seller)
- Next, repeat the steps for the other table and assign a unique name to that table also (i.e. Sales).
- Now, click in any table and then go to the Data tab> click Relationships under the Data Tools group.
- As a result, the Manage Relationships dialog box will appear.
- Here, click New.
- After that, Create relationship prompt will show up. Here, you have to select four fields in this prompt.
- Table: The first table you want to analyze (i.e. Sales)
- Column(Foreign): The common column between the tables (i.e. ID)
- Related Table: Table that looks up the value in the previous table (i.e. Seller)
- Related Column (Primary): The common column between the tables (i.e. ID)
- Then, click OK.
- After that, Close the Manage Relationships box to confirm the relationship.
- Now, go to the Insert tab> click dropdown of the PivotTable > select From External Data Source.
- Now, a new pop-up PivotTable from an external source will appear. Click Choose Connection…
- From the Existing Connection prompt, go to the Tables group and click Tables in Workbook Data Model and click Open.
- After that, the PivotTable field will appear on the right side of the Excel window. Drag the fields to relevant areas. (i.e. Seller=> Rows, Sales=> Values)
- As a result, table will be added to your data model.
Read More: How to Create a Data Model in Excel
2. Applying the Power Query Tool
Power Query is a powerful tool just like its name. With the help of it, you can add table to your data model. Check the ways out.
⏩ Steps:
- First, follow these steps of Method 1 to create table from your data.
- Now, go to the Data tab> click Form Table/Range.
- Next, the Power Query Editor window will appear on the worksheet. From the Home tab of this window> click the dropdown of Close & Load > select Close & Load To…
- Now, a pop-up named Import Data will appear.
- Here, select Only Create Connection and then keep Add this data to the Data Model and click OK.
- After that, go to the Data tab> select Data Model from the Manage Data Model group.
- Now, the Power Pivot window will appear. From the Home tab> select Diagram View from the View tab.
- Here, the relationship will be created in diagram view. This is one to many relationship (Here, * denotes many).
- Next, go to the Insert tab> click dropdown of the PivotTable> select From Data Model.
- Finally, create PivotTable by following these steps of Method 1 for your data model.
Read More: Excel Data Model vs. Power Query: Main Dissimilarities to Know
3. Utilizing the Power Pivot Feature
Another procedure available for adding table to the data model is to utilize the Power Pivot feature. In order to apply this tool to perform your task, just proceed with the steps below.
⏩ Steps:
- First, follow these steps of Method 1 to create table from your data.
- After creating table from your data, go to the Power Pivot tab and then select Add to Data Model under the Tables group.
- Then, these steps of Method 2 will come respectively. Repeat the steps to add a table to the data model.
Practice Section
Here, I am providing you with a practice sheet so that you can practice yourself to add table to data model.
Download Practice Workbook
You can download the practice book from the link below.
Conclusion
So, these are the ways you can follow to add table to data model in Excel. Hope the article has helped you to some extent. If you have any questions, or feedback regarding this article, please don’t forget to share them in the comment box. Have a great day!
