Linked tables returning to many values for each record


New Member
Sep 12, 2008
Hi! I've created my first PowerPivot workbook. I created 11 tables from a single MySQL database, and linked them based on primary/foreign key pairs.

I created a Pivot Table from the results, and dragged two columns into the row section; one from the "main" data table, and the other from a linked table.

The relationship between the two tables is one-many (from the linked table, basically a lookup table, to the main table). For each primary key in the main table, I should see one value from the linked table.

Instead, for each primary key on the main table, the Pivot Table returned ALL the values in the lookup table; it was as if there was no specified link.
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Row Labels</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">27</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Brent Leung</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Briana McIvor</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Chester Grant</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Christopher Lacroix</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Dean Ara</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Delete Me</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Doneen Swart</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Elaine McKay</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Gosia Kamela</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Greg Crompton</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">hello mister2</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Jay Brown</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Jonny Producer</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Karen Wrafter</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Kristin MacCary</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Nik Green</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Producer Producer</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Research Intern</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">Reuben Bradley</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">Rick Mattar</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">Rob McKee</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">Shaun Richards</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">Stephanie Barr</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">Steve Wyatt</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A1</th><td style="text-align:left">Row Labels</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A2</th><td style="text-align:left">27</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A3</th><td style="text-align:left">Brent Leung</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A4</th><td style="text-align:left">Briana McIvor</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A5</th><td style="text-align:left">Chester Grant</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A6</th><td style="text-align:left">Christopher Lacroix</td></tr></tbody></table></td></tr></table><br />

Clearly, I've done something wrong with my linking. I've reviewed a few sites to look at normal linking practices, and as far as I can see, I've not done anything wrong. Can someone help me see what I've missed?

Thanks very much!

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back