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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,201
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,201
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")
 

Forum statistics

Threads
1,089,675
Messages
5,409,665
Members
403,274
Latest member
Lewis1995

This Week's Hot Topics

Top