Use Joiner Tables Between Tables
March 06, 2023 - by Bill Jelen

Problem: I have a top-level budget table with one row per month, region, product. It has 54 rows. I want to create a report comparing the budget table to an invoice table with hundreds of rows.
Strategy: Create three tiny “joiner” tables to act as intermediaries between the two tables. In the figure below, the three row Product table is a joiner table. Build a relationship from ProductB in the Budget table to Product_J in the joiner table. Build a second relationship from ProductA in the Invoice table to Product_J in the joiner table. Build similar relationships, joining Region_J to both RegionB and RegionA. Also create relationships to join the Date field to both DateB and DateA.

When you build your pivot table, all fields in the Rows, Columns, and Slicers should come from the Joiner table.

This article is an excerpt from Power Excel With MrExcel
Title photo by Guillaume Bourdages on Unsplash