Struggling with Basic relationship joins

malraff

New Member
Joined
Jan 25, 2017
Messages
2
hi

i have 2 really simple data models

data set 1

Code
1
2
3

data set 2

TopLevel desc
1 apple
2 banana
3 Carrot


i join the 2 table with a relationship Code = Toplevel, i have no duplicates

but when i use a pivot table to show the data i get

Code Desc
1 apple
banana
Carrot
2 apple
Banana
Carrot


im obviously missing something obvious to a trained eye?

any ideas?

mal
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,202
You only have 1 data model but 2 tables.

A pivot table is a tool to aggregate date, not a query tool. If you want to use a pivot table, you need a measure too. You could simply drag desc to values as well as rows
 

malraff

New Member
Joined
Jan 25, 2017
Messages
2
You only have 1 data model but 2 tables.

A pivot table is a tool to aggregate date, not a query tool. If you want to use a pivot table, you need a measure too. You could simply drag desc to values as well as rows
hi Matt, thanks for reply
i know i am am trying to use power pivot as a db, which would be nice for me in this instance
desc is a text field (description), if i put it in as a value then it wants to count/sum etc the field? where as i just want the text shown

mal
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,202
What version of Excel do you have? If you have Excel 2016, you can write this measure.

=concatonatex(values(table2[Desc]),table2[desc],", ")

if it is 2010/13 you can write this measure

=if(hasonevalue(values(table2[Desc])),values(table2[Desc]),"Multiple Results")
 

Watch MrExcel Video

Forum statistics

Threads
1,090,490
Messages
5,414,849
Members
403,549
Latest member
CascadeDiver

This Week's Hot Topics

Top