Power Pivot Connection On A Unique ID

**ekatecohn** · 12-19-2014, 01:47 PM

I am attempting to self teach and gain a basic understanding of Power Pivot. So far I know that it allows me to create joins, instead of using VLOOKUP, and because I have used a different product in the past, I am aware of the joins concept. That said, I am trying to find out a way to return only the unique values from one data set to another.

For example, data set 1: 8821 rows of data, each one unique. Data set 2, 2837 rows of data, each one unique, and each one contained in data set 1. I join the table on ID, then start to create a pivot. If I happen to have the ID variable from data set 1 in the values section, then it still returns all the values. I suppose in my ideal world, it would limit out the records that are not part of the join. (Just as if I used VLOOK UP, all of those IDS that did not exist in data set two would have turned into #N/A.)

Perhaps I am over thinking this and I need to remember to call on the ID variable from the other data set. But, is there no way to have only the unique ID show? Is there something in my set up or in my join?

PP Issue.JPG

I would also love a Power Pivot 101 resource, I have watched a few webinars, etc. If any one has a great suggestion.

Lastly, I see a lot of "go easy on me" type posts. Is this forum known for not going easy on people? Please go easy on me.

Thanks,

Kate

**ashishmehra2010** · 12-22-2014, 02:19 AM

Hi,

It would be easier if you can post the sample file.

Regards,
AM

**romperstomper** · 12-22-2014, 05:12 AM

You can't specify a join type in PowerPivot. If you add the ID field with more data, you'll get all that data.

Re the going easy part, the moderation in all forums is different - some are very easygoing, others not so much. This is somewhere in the middle, I reckon (on average - it varies from mod to mod as anywhere)

**ekatecohn** · 01-20-2015, 03:52 PM

I have created sample files, I hope this will help illustrate my challenge. Sample file 1 has 1208 unique IDs. Sample file 2 has 900. Pivot from Sample 1 and 2 has both sets of IDS, and then in the row, a data element that is from Sample file 1. Because I have created a relationship between the ID data element in both files, I (wrongly) assumed that the ID count from Sample file 2 would show up appropriately in the pivot (meaning, counting the IDS in as they relate to the data element I have pulled into the pivot for row.

Yet when I read the reply from romperstomper, it appears that will not be possible.

Thank you both for the reply.

Kate

**romperstomper** · 01-21-2015, 05:13 AM

It seems that was created in 2013, though your profile says you use 2010, so I can't open it now. Anyway, if you use a data field from File 1, you will get all ids from file 1.