mr. some casts once arrived easy excel powerpivot for the data analyst five
defining relationships
hey welcome back to the mr. excel NetCast I'm bill jelen l in this episode
I want to talk about manually defining a relationship now it's possible to go
ahead and create the pivot table and let Excel try an auto detect the
relationship but if you're a better safe than sorry kind of person
let's just define the relationship and boy is it easy to define this
relationship so let's take a look at our data
here's my 1.8 million road datasets you have a field called store ID and then
here's my 144 row lookup table it has a field called store and there's a
one-to-one relationship for each store ID on the 1.8 million rows will be a
corresponding store here and of course you know before powerpivot this would be
a lot of vlookups to join this data together 1.8 million rows of the hookups
for every single field here we take forever to calculate watch how easy this
is to do so I start here on the store ID field and I want to define a
relationship
I can right click on the heading and say create relationship and because i've
started on that field
this is already filled in we're starting from the demo table the store ID column
i choose related lookup table store info and then choose the store field and
click create and that's it I've now joined these two tables together which
allows me to create pivot tables very easily from both tables also allows me
to use the related function over here to create a calculated column that would
rely on data in that other table very easy easy way to go but thank you for
stopping by to see you next time for another NetCast from mr excel