Diagnosing and Fixing Relationship Problems

Building a model that looks correct in diagram view is not the same as building one that produces correct numbers. Silent wrong results — totals that look plausible but do not match reality — are more dangerous than error messages, because they give no indication that anything is wrong.

The Four Root Causes

1. Duplicate values in the primary key
   
   The dimension table contains the same key value in more than one row. Because the one side of a relationship requires unique values, Power Pivot refuses to create the relationship and shows an error.
   
   

   Error: "relationship cannot be created because each column contains duplicate values"
   
   

   Fix: Data tab → Remove Duplicates. Tick "My data has headers", select the key column, click OK. Refresh Power Pivot before retrying.

2. Mismatched data types
   
   The key column in the dimension table is stored as a different data type than the foreign key in the fact table — for example, Product ID is a number in the Products table but text in the Sales table. Power Pivot allows the relationship to be created but the analysis will be incorrect because the values do not truly match.
   
   

   Symptom: relationship creates without error but pivot totals are wrong or rows are missing.
   
   

   Fix: align the data types in both tables so both columns are the same type and formatted identically. Refresh and rebuild.

3. Orphaned foreign keys
   
   

   The fact table contains a key value that does not exist anywhere in the dimension table. For example, a Sales row references Customer ID C099 but no such customer exists in the Customers table. Those rows are silently excluded from any analysis — they do not appear in pivot tables and their values are dropped from totals.
   
   Symptom: Pivot totals appear slightly lower than expected; a blank row may appear in certain breakdowns.
   
   Fix: use COUNTIF to find fact table keys that do not appear in the dimension table. Add the missing dimension rows or correct the key values in the fact table.

4. Blank key values
   
   

   Empty cells in the key column of either table. A blank in the dimension table's key column makes it impossible to match those rows. A blank in the fact table's key column means those sales rows cannot be assigned to any dimension entry — they are grouped into an unnamed blank row in pivot outputs, distorting every breakdown.
   
   Symptom: a blank row appears in pivot breakdowns, absorbing sales that belong to unidentified customers or products.
   
   Fix: filter the key column for blanks using the dropdown. Delete the row or fill in the correct key value. Refresh all and rebuild the pivot.

The Troubleshooting Checklist

When a relationship is not behaving as expected, work through this checklist in order. Each item rules out one class of problem before moving to the next.

• Step 1 — Confirm the relationship exists

  Open Power Pivot → Design tab → Manage Relationships. Verify the relationship you expect is listed, with the correct tables and correct columns on both sides. It is easy to accidentally link on the wrong column.

• Step 2 — Check the direction

  In Diagram View, look at the 1 and * indicators on the relationship line. The dimension table end must show 1 and the fact table end must show *. If they are reversed, delete the relationship and recreate it by dragging from the dimension table.

• Step 3 — Check for duplicates in the primary key

  On the worksheet, click into the dimension table. Select the key column and use Data → Remove Duplicates (on a copy) or a COUNTIF formula to check whether any value appears more than once. A quick formula approach: add a temporary helper column with =COUNTIF($A:$A, A2) and filter for values greater than 1.

• Step 4 — Check data types

  Click any cell in the key column of the dimension table and look at the format displayed in the Number group on the Home tab. Do the same for the foreign key column in the fact table. Both should be the same type — Text or Number, not mixed.

• Step 5 — Check for orphaned foreign keys

  Use a COUNTIF on the fact table's foreign key column, referencing the dimension table's key column: =COUNTIF(Customers[CustomerID], Sales[CustomerID]). Any row returning zero has a foreign key value that does not exist in the dimension table. Investigate and correct those rows.

• Step 6 — Check for blanks

  Filter the key column of the dimension table and check whether any blank rows exist. Filter the foreign key column of the fact table and check the same. Blanks on either side need to be resolved before the relationship will behave correctly.

Task

In this task, you will work with a deliberately broken version of the project workbook. Your goal is to identify the relationship problems, fix the source data, and confirm that the model works correctly again.

This task focuses on the same troubleshooting habits demonstrated in the video: inspect the source tables first, fix the data on the worksheet, refresh the Data Model, then validate with a PivotTable.

Repair the workbook so that the model behaves correctly and can support clean analysis across all four tables.

Step 1 — Inspect the model

Open the workbook and review all four sheets: Customers, Products, Dates, and Sales.

Then open Power Pivot → Manage and switch to Diagram View or Manage Relationships.

Identify which relationships are missing, failing, or likely to behave incorrectly based on the data you see in the tables.

Step 2 — Find and fix the data issues

This workbook contains three types of relationship problems covered in the lesson. Your job is to locate them and correct them.

Check for:

• Duplicate values in a dimension table key column;
• Blank values in a dimension table key column;
• Fact table rows whose date has no matching row in the Dates table

Fix the problems directly in the worksheet tables.

Step 3 — Refresh the model

After making your corrections, go to Data → Refresh All so the Data Model updates.

Then return to Power Pivot → Manage and confirm that the model can support the correct relationships.

Step 4 - Validate with a PivotTable

Create a PivotTable from This Workbook's Data Model and use it to confirm that your fixes worked.

At minimum, test the following:

• Region from Customers with Total from Sales;
• Category from Products with Total from Sales;
• Year or MonthName from Dates with Total from Sales.

Your PivotTable results should be complete and plausible, with no suspicious blank row caused by bad keys.

Success Criteria

Your task is complete when:

• The duplicate key issue is fixed;
• The blank key issue is fixed;
• The missing dates problem is fixed;
• The model supports correct analysis across all four tables.

Your PivotTable validation shows believable totals grouped by dimension values with no blank rows.