4.1-Power Pivot & Power BI, Understanding Table Structure

Date: September 3, 2025

Status: Draft

Tags: Excel Data Analysis Power BI Power-Pivot

Understanding Table Structure in Excel and Power BI

Key Concepts in Table Structure

Primary Key

A primary key is a unique identifier for each record in a table. It ensures that each row in a table is distinct and can be uniquely referenced. For example:

• In a table containing customer data, a column labeled “Customer ID” might serve as the primary key, with each customer assigned a unique ID (e.g., C001, C002).

• Primary keys must be unique and cannot contain null values.

Foreign Key

A foreign key is a column in one table that refers to the primary key in another table, establishing a relationship between the two tables. For instance:

• If Table A has a primary key “Customer ID” (e.g., C001), Table B might have a column “Customer ID” as a foreign key to link records in Table B to Table A.

• Foreign keys maintain referential integrity, ensuring that relationships between tables remain consistent.

Lookup Table (Dimension Table)

A lookup table (also called a dimension table in Power BI) contains descriptive or categorical data used to provide context to numerical data. For example:

• A lookup table for products might include:

  • Product ID: Unique identifier for the product.

  • Product Name: Name of the product.

  • Category: Product category (e.g., Electronics, Clothing).

  • Price: Unit price of the product.

• Lookup tables are used to store attributes or demographic variables that categorize or describe data in other tables.

Fact Table

A fact table contains quantitative data, such as numerical values representing metrics like sales, revenue, or quantities. For example:

• A fact table for sales might include:

  • Order ID: Unique identifier for each order.

  • Customer ID: Foreign key linking to the customer lookup table.

  • Product ID: Foreign key linking to the product lookup table.

  • Quantity: Number of items sold.

  • Revenue: Total sales amount.

• Fact tables typically store transactional or measurable data and are connected to lookup tables via foreign keys.

---

Data Modeling

Data modeling involves defining how data from different tables are related and connected to form a cohesive structure for analysis. A well-designed data model ensures efficient querying and accurate reporting.

Relationships in Data Modeling

• Connected Data: Some datasets share common fields (e.g., Customer ID or Product ID) that allow them to be linked through primary and foreign keys.

• Disconnected Data: Some datasets may have no direct connection to each other but can be linked through a central table that connects to all datasets. For example:

  • An Orders table might connect to both a Customers table and a Products table via their respective IDs.

  • The Customers and Products tables may not have a direct connection but are linked indirectly through the Orders table.

Example of Data Connections

Consider three datasets:

1. Customers: Contains customer details (Customer ID, Name, etc.).

2. Products: Contains product details (Product ID, Name, Category, Price).

3. Orders: Contains transactional data (Order ID, Customer ID, Product ID, Quantity, Revenue).

• The Orders table serves as a central table, linking Customers and Products through the Customer ID and Product ID fields, respectively.

---

Power Pivot in Excel

Power Pivot is an Excel add-in that enables advanced data modeling and analysis, particularly for handling large datasets from multiple sources. It supports the creation of relationships between tables and allows the use of DAX (Data Analysis Expressions) for complex calculations.

Setting Up a Data Model in Power Pivot

To create a data model in Power Pivot:

1. Import Data:

   • Open Excel and load the first dataset (e.g., Orders dataset with columns like Order ID, Customer ID, Product ID, Quantity).

   • Go to Power Pivot > Add to Data Model to include the dataset in the data model.

   • Repeat for additional datasets (e.g., Customers dataset with Customer ID, Name; Products dataset with Product ID, Name, Category, Price).

2. Create Relationships:

   • In Power Pivot, switch to the Diagram View or use the Manage Relationships option.

   • Connect related fields:

     • Link Customer ID in the Orders table to Customer ID in the Customers table.

     • Link Product ID in the Orders table to Product ID in the Products table.

3. Build a Pivot Table:

   • Create a Pivot Table in Excel to analyze the data.

   • For example, you can calculate total revenue by combining fields like Revenue from the Orders table and Name from the Customers table.

   • Note: If fields from unrelated tables (e.g., Revenue from Products and Name from Customers) are used without proper relationships, the results may be inaccurate.

Updating the Data Model

• If new data is added (e.g., new products in the Products table or new orders in the Orders table), refresh the data model:

  • Go to Power Pivot > Manage > Refresh to update the data model with the latest changes.

• This ensures that all calculations and relationships reflect the most current data.

DAX (Data Analysis Expressions)

DAX is a formula language used in Power Pivot and Power BI to create calculated columns, measures, and custom calculations. Key points:

• DAX formulas work in both Power Pivot (Excel) and Power BI.

• Common DAX functions include:

  • SUM: Calculates the sum of a column.

  • RELATED: Retrieves related data from another table based on a relationship.

  • CALCULATE: Modifies the context of a calculation.

• Example: To calculate total revenue by customer, you might use a DAX formula like:

  Total Revenue = SUM(Orders[Revenue])

• DAX will be covered in detail in a future session.

Best Practices for Table Structure and Data Modeling

1. Ensure Unique Primary Keys: Every table should have a primary key to uniquely identify records.

2. Define Clear Relationships: Use foreign keys to establish relationships between tables, ensuring referential integrity.

3. Use Lookup and Fact Tables Appropriately:

   • Store descriptive data in lookup (dimension) tables.

   • Store measurable data in fact tables.

4. Leverage Power Pivot for Large Datasets: Use Power Pivot to manage and analyze data from multiple sources efficiently.

5. Regularly Update Data Models: Refresh the data model whenever new data is added to maintain accuracy.

---

References

• No external references provided in the original document.

• For further learning:

  • Microsoft Excel Power Pivot Documentation: https://support.microsoft.com/

  • Power BI Documentation: https://docs.microsoft.com/en-us/power-bi/

  • DAX Guide: https://dax.guide/