So here we have a PDF with 21 pages of data, which has data about car sales by states
in the United States. So what we can do here is highlight the columns in the table
we want to copy, Ctrl C, and going to excel, do Ctrl V. And here the problem is
that all the data comes into one column and we want in three columns. So we're
going to use flash fill to be able to take care of that. So we copy the rest of the
states and then now we're going to be able to flash fill. So here I type Alabama and then
the moment I type the s in Alaska it recognize the flash fill that I'm looking
for the pattern of the states in the first part of the Cell. I click
Enter and it automatically fills the rest. Now I'm going to the second column
which is the total cells in million. So here
Alabama has nearly nine billion dollars in sales in car. So I type a dollar sign 8924
the moment I type the dollar sign it automatically recognize and flash fill.
and then the last part is the average sales per dealership, here in this case
is a little over 30 million sits in thousands. So 30 comma 046 and the moment I type the dollar
sign it recognize the pattern. So here I moved from one column using flash fill
to three columns. I can delete this one, I still have a problem is that this is a
text field. I highlight my numbers and simply convert to numbers by clicking
on the exclamation point. Now I have data with numbers so I can click this is
state, I'm going to name the column, this is the total sales and this one is average
revenue per dealership. We're going to abbreviate dealership. I can highlight the entire
table
and convert it to a table. Insert table, my table has headers, yes, and
I've created my table, I'm going to give it a name here, call it Sales. So in the interest
of time, I've used the exact same process to create four more tables from
that PDF that are just on the right of my newly created table. These tables tell
me the number of car dealership in that state, so Alabama has 297 car dealerships
the number of employees selling car Alabama has around 14,000 which gives them
an average of 47 per dealership. The weekly earnings on average of those
employees $870 and in order to be able to join all those states we created
mapping table with state which I also group it into different regions. So I
need to add my newly created table to my data model, so I click in a cell in
the table, click on PowerPivot and click Add to data model. As you can see I already
created the tables for my data model. So I'm going to join all those tables together
through my mapping table that has state and region. I go from state in NewCarDship
to state mapping table. Weekly earnings, Sales and Employees
All my tables are joined, this is my data model. Four tables with data joined together through this
mapping table