Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel Data Model vs. Power Query: Main Dissimilarities to Know

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

Dataset for excel data model vs 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).

Excel Data Model vs. Power Query: Dataset Location

  • 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.

Showing worksheets

  • 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.

Excel Data Model vs. Power Query: Transforming Data

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.

Adding Sales column

  • Then, enter a name for the column, here it is “Sales” >> construct the equation given below >> hit OK.

=[Unit Price]*[Quantity]

Using multiplication operator

Finally, this adds a new column, as shown in the figure below.

Results of Transforming Data with Power Query

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.

Excel Data Model vs. Power Query: Loading 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.

CLosing and loading in Power Query

  • 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.

Creating connection

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.

Excel Data Model vs. Power Query: Establishing Relationship

  • Second, choose the tables, in this case, “Sales_Data” and “Location_Data” >> click on OK.

Establishing relationships between two tables

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.

Using Combine queries and Merging them

  • Not long after, enter the table names (“Sales_Data” and “Location_Data”) >> highlight the common column (“Sales Rep”) >> press OK.

Performing left outer join

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.

Excel Data Model vs. Power Query: Analyzing Data

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.

Inserting PivotTable Report

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.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo