Eshrak Kader
Indeed, Microsoft Excel is a ubiquitous tool for organizing and analyzing data. Oftentimes, people wonder what the difference is between an Excel Data Model and Power Query. Fortunately, in this article, we’ll answer this question and explore all the nitty-gritty of Excel Data Model vs. Power Query.
Download Practice Workbook
What Is Data Model?
In simple terms, a data model enables us to combine information from many tables to create a relational data source within an Excel workbook. Moreover, data models are utilized openly in Excel and provide tabular data for PivotTables and PivotCharts.
What Are Power Pivot and Power Query?
First and foremost, let’s start with a quick explanation, so you don’t have to spend all day on this. Simply put, Power Query imports the raw data and shapes it (merging columns, removing rows, altering data type, etc.) according to the user’s needs. In contrast, Power Pivot helps to visualize, perform analyses, and draw conclusions from the dataset.
5 Benchmarks to Demonstrate Excel Data Model in Power Pivot vs. Power Query
Now, let’s assume the Sales Dataset shown in the B4:G16 cells which contains the “Date”, “Item”, the names of the “Sales Rep.”, “Unit Price”, “Quantity”, and “Sales” in USD. Additionally, the dataset below shows the “Region” of each “Sales Rep.” respectively. Here, we want to utilize this dataset to demonstrate 5 differences between the Excel data model and Power Query. Hence, without further delay, let’s glance at each difference with proper illustrations.
📃 Note: Throughout this article, we’ll employ Power Pivot to make a data model in Excel and compare this process with that of Power Query.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your preference.
1. Excel Data Model vs. Power Query: Dataset Location
First of all, one major difference between Power Pivot (Excel data model) and Power Query is the location of the data in the worksheet(s).
- In the case of the Power Pivot (Excel data model), the dataset must be in the same worksheet, so they can be prepared and combined further down the pipeline.
- For instance, the image below shows the dataset that will be imported using the Power Pivot (Excel data model).
- However, when using the Power Query editor, the datasets can be in different worksheets, for example, the “Dataset-1” worksheet refers to the “Sales Data” table.
- Next, the “Dataset-2” worksheet points to the “Location of Store” table.
Read More: How to Create a Data Model in Excel (3 Handy Ways)
2. Excel Data Model vs. Power Query: Transforming Data
Alternatively, transforming the imported data using Power Pivot (data model) and Power Query also differs greatly between the two approaches. Normally, the Power Pivot (Excel data model) performs data analysis, so it is better to perform data transformation beforehand.
📌 Steps:
- In the first place, add a “Sales” column >> type in the formula given below.
=E5*F5
For example, the E5 and F5 cells represent the “Unit Price” of each item and the “Quantity” sold.
In contrast, we can easily add a column in Power Query by following the steps shown below.
📌 Steps:
- Initially, go to the Add Column tab >> click the Custom Column option.
- Then, enter a name for the column, here it is “Sales” >> construct the equation given below >> hit OK.
=[Unit Price]*[Quantity]
Finally, this adds a new column, as shown in the figure below.
Read More: How to Create Calculated Field in Pivot Table Data Model
3. Excel Data Model vs. Power Query: Loading Dataset
For one thing, loading data with the Power Pivot (Excel data model) is a very simple process, as shown in the steps below.
📌 Steps:
- To begin with, move to the Power Pivot tab >> press Add to Data Model to import the dataset.
The same thing is different from the Power Pivot in the case of the Power Query editor; the process involves a few more steps, so just follow along.
📌 Steps:
- At the very beginning, click the Close & Load option drop-down>> choose Close & Load to.
- In turn, this opens the Import Data window >> click Only Create Connection >> press OK.
- Lastly, repeat the same process if you want to load multiple datasets.
Read More: How to Manage Data Model in Excel (with Easy Steps)
4. Excel Data Model vs. Power Query: Establishing Relationship
Conversely, another noteworthy deviation between the Excel data model and Power Query occurs when establishing a relationship between the datasets. So, let’s see it in action.
📌 Steps:
- First, in the Power Pivot window, navigate to Diagram View >> Right-click and select Create Relationship option.
- Second, choose the tables, in this case, “Sales_Data” and “Location_Data” >> click on OK.
On the other hand, follow the steps below to establish relationships in the Power Query editor.
📌 Steps:
- To start with, in the Excel window, go to the Get Data drop-down >> proceed to Combine Queries >> choose the Merge option.
- Not long after, enter the table names (“Sales_Data” and “Location_Data”) >> highlight the common column (“Sales Rep”) >> press OK.
Read More: [Fixed!] Excel Data Model Relationships Not Working (6 Solutions)
5. Excel Data Model vs. Power Query: Analyzing Data
Last but not least, data analysis using the Power Pivot (Excel data model) requires fewer steps, in fact, we can insert a PivotTable with the click of a button.
📌 Steps:
- At this time, jump to the PivotTable drop-down >> select PivotTable.
In contrast, to insert PivotTable from the Power Query editor, follow these steps.
📌 Steps:
- Initially, navigate to the Close & Load to option.
- Later, check the PivotTable Report option >> hit OK to close the window.
Read More: How to Get Data from Data Model in Excel (2 Easy Methods)
Conclusion
Hopefully, this tutorial has provided you with helpful knowledge on Excel Data Model vs. Power Query. Now, we recommend you apply all this know-how to the practice dataset by downloading the practice workbook. In addition, feel free to comment and provide your valuable feedback.
