Modern Data Analytics in Excel

Chapter 7. Creating Relational Models in Power Pivot

Chapter 6 introduced the fundamentals of Power Pivot as an effective tool for data analysis and reporting, especially when working with data from multiple sources. This chapter offers a demonstration on how to use Power Pivot for relational data modeling.

Connecting Data to Power Pivot

As highlighted in Chapter 6, the Data Model serves as the foundation of Power Pivot, facilitating the creation and management of table relationships for effective data computation and analysis. Power Pivot streamlines this task with an intuitive drag-and-drop interface. This chapter delves deeper into the Data Model, using the ch_07.xlsx file. This file contains a retail sales dataset often referenced within the analytics community and is found in the ch_07 folder among the book’s resources.

In the example from Chapter 6, the Data Model was predefined. In this chapter, we’ll need to define it manually.

Although direct connections to data sources via Power Pivot are feasible, it’s recommended to channel data through Power Query first. This approach provides a convenient platform to establish any recurring data cleaning procedures on these tables when necessary.

To get started, import the orders table into Power Query using the Data → From Table/Range steps. Bypass any data transformation steps and proceed to Close & Load → Close & Load To on the Home tab.

To load this query to Power Pivot, choose to create the data as a connection only, and then ...