This screen should look familiar with the pivot table and the empty relationships box.
It's where we were about halfway through our last lesson when we introduced the relationship manager
in this lesson, instead of manually creating all of our relationships, we're going to auto detect
the relationships in our data.
Before we actually click on Auto Detect, let's talk about what Excel looks for when it's creating the
relationships.
Excel looks for three things.
One is matching field names between tables.
So let's create a new relationship to demonstrate this in the new relationship window.
I'm going to select my sales table on the top and the store name for the column in my related table.
I'm going to select stores now.
Notice that the related column automatically populates itself with the starfield.
Since we picked the two tables and had one of the column field selected, Excel was able to choose the
correct field.
Let's cancel out of this and discuss the other component that Excel considers just having two columns
that match up isn't sufficient for Excel to create a relationship between the two tables.
Our sales and purchase orders tables both have a store field, but they shouldn't get matched up since
they have multiple duplicates between them.
The second check is for one of the tables to have a unique set of entries in the matching field.
So with our sales and purchase orders, while they both have store columns, neither store column is
a unique list.
The third and final item that Excel considers is to look at existing pivot tables in the workbook to
determine where relationships are expected to exist.
This is particularly important for certain types of tables that we want cross-trained in the future.
With all of that said, let's go look at our pivot table right now.
I only have sales and store showing and we know that the field names match up between those two.
So if I click on Auto Detect, a window will pop up and it looks for relationships and identifies and
creates one new relationship.
I can press close and we can see that our relationship has been created and it's active.
Let's go structure our data so that we can protect all of the relationships we need on our calendar
table.
This means I need to change the dates field to date on our labor table.
That means I need to change the month field to date and the store name to store.
Once we've done that, I need to go back to our pivot table and add month to the table from the calendar
table and then I need to add purchase order and employee wages to the value section.
Now we're almost ready to order, detect all of our relationships if I run the detection now, Excel
will only pick up three out of our six relationships because it will miss the date relationships.
To catch these, we need to venture into the data model and do one quick thing.
So I'm going to click on the manage data model button on the data tab of the ribbon and go to the calendar
tab here.
I'm going to click on the design tab and choose the mark as date table command.
I don't know why this has to be done, but it just does.
Once that's complete, I can close the data model, open our relationships and click on Auto Detect
to find five more new relationships.
This relationship detection feature can make setting up simple data sets like this one fast and easy
with just a little bit of data preprocessing.
You get your data, you throw it in Excel, you add it to the data model, create the pivot table you
want, and then hit auto detect to get to relationships.
It's really pretty impressive.
However, I vastly prefer to define the relationships myself and I recommend that you not rely too heavily
on the auto detect feature.
You'll find that power pivot is much friendlier and much more powerful.
If you're just a little bit familiar with your data structure, the more familiar, the better.
This lesson nearly closes out our chapter covering the very basics of power.
Pivot in the next lesson, I'll offer some tips and suggestions for how to use power, pivot and the
relationships to handle a few scenarios with an excel excluding naturally, this particular example.