Welcome to HighLine Excel
2016, Class Video Number 17. If you want to download
this Excel workbook file, Business 218 Video 7 Start. There's also the finished file. There's also PDF files, and
the zipped folder with text files we're going to need
for the dashboard project. You can click on the
link below the video. Wow this is a video about
how to build dashboards. We're going to have
pivot tables, charts, conditional formatting. Our data will be stored
in the data model. And we're going to get to see
our first DAX formula, which are formulas for pivot tables. Here's our entire
list of all the steps in building our dashboard. I want to go over
to the finished file and click on the
Dashboard Sheet. Here's what we want
to try and accomplish. Here's a slicer And
when I click on 2017, my line chart for revenue
for all the months, my bar chart for the different
sales reps and the revenue amount, and my cross-tabulated
table with product and region, and our conditional
formatting all will change when we click on the slicer. Not only that, but one of
the hallmarks of a dashboard is when new data
becomes available, I can simply refresh. And I'm going to use the
keyboard Control-Alt-F5. And now there's a lot
of data being imported. And there are data source
has been updated with 2018 and instantly appears
in our slicer and all of our charts,
cross-tabulated pivot table, and our conditional formatting. All can see that. All right let's go and
create this dashboard. We're going to go
to the Start file and I actually want to
look at the sheet notes just for a moment. A dashboard is simply
a data visualization that presents useful
information and metrics that will update automatically when
new data become available. Dashboards can contain various
types of visualizations from tables of data charts,
data validation pictures, or other visualizations. Now let's go over to
the sheet Dashboard. Now step one is connecting
to the data source. And actually before
we do that, let's go back to the
topics sheet here. We have a lot of steps here
in creating this dashboard. We have to import the data. We have to clean the data. We have to create four
different pivot tables, two different charts, and
some important formulas. So this is a lot
of steps that go into creating this dashboard. Let's go over to
Dashboard sheet. And step one is to
import the data. And we are going to use
data in Get & Transform. But before we do that, let's
go look at Windows Explorer and look at the folder and files
that we're going to import. Now when you download this,
you download the zipped version of video 17 import files. When you unzip it, there will be
a Start folder and a 2018 text file. I'm going to double
click to open up and look at what's
in the Start folder. Because these are
the files that we're going to import to start
building our data model, our pivot tables,
charts and so on. Later we'll update this
folder with the latest data. And then everything will
update inside of Excel. Now, why text files? Text files are often
used to communicate between data storage system
or systems that use the data. So the 2016 and '17 data was
exported into this text format. All right, I'm going to
click the Back button. When we are over in
Excel, we're actually going to point Excel
to that Start folder. Right now there's only
two files in there, but later when we add
more files to that folder, Excel will automatically
see them and import them when we refresh. All right let's
go over to Excel. Now to import those text
files, we go to Data. And Get & Transform. Now Get & Transform
group in the new query we're going to use this
is all new in Excel 2016. This whole group right here
used to be a separate tab called Power Query. And you used to have to download
it and install the add-in. But in 2016 Get & Transform
is our Power Query. We go to New Query from File
and down here from Folder. Our browse window will open up. Click Browse. Now there's our first folder. And we want to select
the Start folder. What were telling Power
Query is please import all files from
inside that folder. When I click OK, click OK. If you get this intermediate
step, click Edit. We get inside our
power query editor. We get a list of all of
the files and information about the files. Now we want to come over
here and be sure and name it. We always want to name our
query and the table that will be imported into the
data model a smart name. So I'm going to call this
something like Transaction Table and Enter. If we need to come back
and edit our query, that's the name of the query. Later this will be the name of
the table and the data model. Now we are importing
only text files. If we had other types of filing
we needed to filter them out, we would use this Filter button. But we don't. We're only going to have
text files in that folder. So we're not going to
need any of these columns. I come to the Content column. Right click, Remove
Other Columns. And instantly they're all gone. Now watch this, our
steps for our query are being memorized over here. Later when we need to refresh
because there's new data, these steps will
automatically be repeated. Now content, there's this little
double downward pointing arrow. We click on this to expand our
text files and check that out. There's all of our columns. Now part of the import process
is also cleaning our data and transforming it. Now there's a bunch of
columns we don't need. Transaction number
we don't need, so I'm going to click on that. Before I click on
website, I'm going to hold the Control
key because I don't need that column either. Then I'm holding Control
units, discount, price. All of these columns
we do not need. Particularly with big data, you
want to remove any extra data that you don't need. Now I'm going to right
click, Remove Columns. Now look over here. We have a bunch of
steps memorized, including Promote Headers. It actually took the text
file and the very first record it promoted as the headers. Now one problem is we
have multiple files. And each one of those files
have the same field names. So way down on the
bottom of our data set, there are some rogue or extra
field names we don't need. The field names down
below in the dataset are being treated as a record. So we're going to come
up to one of the columns. And I'm going to pick the
column with the fewest number of unique records, and I'm going
to filter out that extra field name. So I'm going to
click the drop down. Now one thing that
happens with large data is this filter list
displayed, it says, list may be incomplete. So please click the Load More. For larger datasets that
takes a while, right? But instantly now we
see a unique list. And there's our field name
that's listed down below. By unchecking this,
we're going to filter out type in the Type column. It will also filter
out the entire record which means it will filter
out the extra field names in the tables listed below. Now when I click OK, that
step is memorized over here. When we re-run this,
anytime we add new tables, that step right there will
filter out the extra field names. Now, there's one other bit of
data we want to filter out. The Try Fly product does not
very many transactions, not much revenue. And we don't want to analyze
that particular product. But I'm actually going
to make a mistake and forget to filter it out. And later after we create
our charts and pivot tables, we'll notice our mistake. We'll actually come back to this
query, edit it, click Refresh. And everything will update. So one amazing thing
about power query is all of these steps for
importing, transforming, and cleaning are memorized,
whether we add new files, or need to come back here
later to refine our cleaning and transforming. That makes power query amazing. Now we want to do
one last thing, which is check each column. We want to check the
data type to make sure we got the right data type. Date and time is perfect. These ones are text. Each one of them
looks like text. And this one is decimal number. Now sometimes when
you have numbers, you'll see that
they show up as text and that causes big
trouble later on if you try to build pivot tables. So you want to make sure
and check that initially. If you forget, you can always
come back and edit later. All right now we're
ready to load this and I'm not going to click
Close and Load right there. I want to click
Close and Load 2, because we want to send
this to the data model. Now by default it says Table. We do not want to dump
this data into a table. We want to say only
create a connection and then come down and check
Add This to the Data Model. Now there's a bunch of
advantages to the data model. In particular, if you
have a lot of data, you don't want to load it
into Excel sheet in a table, because it takes
up a lot of space. Not only that but there's
a lot of efficiencies and we'll see for the first
time in this video formulas that we can create
in the data model. You can build relationships
between tables. We saw that in an earlier video. So we're dumping it
into the data model, only create a
connection data model. Let's click Load. We could see over here
on the right there's our table, 5,918 records. Now we want to go look
at the data model. We can either go
to Data and there's our managed data model or power
pivot and manage data model. And there is our table. One, two, three, four, five,
six columns got imported. We can see there's our name. That's the name of the
table on the data model it's also the name of our query. There's the number of records. Earlier in the
class, we actually went to diagram view because
we had multiple tables and we needed to
create a relationship. In this video we
have just one table. Now we're actually
going to analyze revenue and we'll analyze it
based on year, and month, sales rep region, and product. Now we'll come back
to the data model later and look at formulas and
also some extra columns that are going to be added to
our table here when we group in the pivot table. Now I want to create
our first pivot table. So I'm going to click this. I definitely want
on a new sheet. Click OK. I'm immediately going to come
and double click and name this sheet
MonthReport and Enter. Now our first pivot
table is going to-- and I'm going to drag
the fill list over here-- is going to be a year
and month revenue report. Now this table and all of the
fields are from the data model. In earlier versions
before Excel 2016, we were not allowed
to group a date field if it was from the data model. We actually had to
build a calendar table and build a relationship. Now there are still
huge advantages to building the calendar
table and relationships. Because a lot of that time
intelligence functions and for big data, it has
some calculation advantages. But if you have a single table
with a date column in the data model, it would be pretty
convenient to simply drag this down to Rows and be
able to group it. Well they added that
feature in Excel 2016. So watch this. Brand new, if I drag a
date field from the data model down to rows, watch this. It takes a second,
because it actually creates a bunch of new columns
over in the data model. But there it is, year and month. I can drag the quarter off. I don't need this
date and time either. If I come over and right-click
Expand, Expand Entire Field. There that is simply amazing
that the data model can now group by month and year. Now in the last
week of our class, we'll talk a lot more
about the data model. And we will see the advantages
to a calendar table. But if you have a single
table in your data model and you want to automatically
group by month and year, that is beautiful
that they added this. Now let's go look
at the data model. Alt-Tab, there it is. One, two, three,
four extra columns. That is pretty amazing
to simply drag and drop. We don't ever even have to know
anything about these columns, right? We're just using the
data model because it's efficient for various reasons. And boom, there it is. Now our next step is
instead of clicking and dragging net
revenue down to values and using the built
in sum function inside the pivot table,
which is by the way called an implicit calculation,
we want to explicitly build a formula that will
calculate net revenue. Now this is the first time we're
going to see a DAX formula. These are formulas specifically
built for pivot tables that exist in the
data model only. The advantage that we
will see in this video is that a new
field will be added called revenue in our case,
because we'll name it revenue. And we will create
the calculation and add the number formatting. And when we drag this field
to multiple pivot tables, it will automatically calculate
and automatically have the number formatting. Now again later in
this class we'll learn more about DAX formulas
and different functions. But here, this is the Power
Pivot ribbon tab calculations. And oh, they're called measures,
which are really formulas. Now I want to go
over to the notes because there's
some terminology we want to talk about Pivot
tax measures are really formulas for pivot tables. Now what does DAX mean? It means Data
Analysis Expressions. Expressions is a
synonym for formulas. And because what we're doing
is data analysis or business intelligence, they
call them D-A and the X comes from the second
letter in expressions. Now you have to have Power Pivot
to create these DAX measures. We want to mention the
different words that mean formulas in a pivot table. You might see DAX formulas,
as you might see DAX measures. You might hear just
measures, just DAX. You might hear
calculated columns. Because Microsoft in 2010
had an add-in for Power Pivot called measures. Then when they
introduced Excel 2013, they changed the name
to Calculated Columns. And now that we're back
in the new 2016 Excel, they called it measures again. So any of these
words you might see. But all they mean
is we're building a formula for our pivot table. Now DAX measures are amazing
because they calculate very fast on big data. We can create a formula once and
add it to as many pivot tables as we want. The number formatting we
actually add at the moment that we create the formula
and then it actually gets carried around with the formula. So whenever you use it, the
number formatting is there. And then there are
many more functions in DAX formula
language than there are in a normal pivot table. Now let's go back over
to monthly report. Now, here it is, Power
Pivot ribbon tab. We go to Measures, New Measure. Now there's the table name. And that's important
because whatever table you attach this
formula to, that's the field list that it will show
up in in your pivot table field list. It also will be stored in the
data model over below the table and we'll look at that
after we create it. The measure name,
this is the name that will show up in the field list. So I'm just going
to call it Revenue. Tab, and description,
DAX measure to add total net revenue. And then I come down here
and I'm going to type S-U-M. Just like over in Excel,
you'll get a drop down when you type the letter. We want the Sum function. I'm going to type
an open parentheses. And check that out. Here down a little
bit further, there's our table inside the data model. Notice table name and
then in square brackets, there's our field names. This is just like
our table formula nomenclature that we've been
using with our Excel table feature throughout the class. That's the one we want. Once we select it
we can hit Tab. You can also once we
say Open Parentheses, we can use our arrow
keys to arrow down. Once you find the
field you want, you can hit Tab, then
close parentheses. Now we come down here. I absolutely love this
about our DAX formula is we can attach a
number formatting I'm going to say, hey,
zero decimal places, because this is a
high level report. When I click OK,
there's going to be a new formula that
automatically gets dumped into our pivot table. It shows up over here
in the field list. Let's see if I can
drag down the bottom. And there it is. In 2016 they even have an icon
to say, this is a function. In earlier versions they
didn't have that f of x. And if we go look
at our data model-- and I'm using Alt-Tab-- there
it is, down below the table. If you click here, you can
see the formula up here. You could actually just come
over and create them over here. If you create them
over here, you create the actual field name of
colon, an equal sign, and then your formula. We will actually do that a lot
in the last week of our class. All right, let's go back
go over to our pivot table. So there it is. Now I'm actually not
going to change the report layout to show field names. I'm just going to type the
name, month, year, and Enter. Now this pivot table
is actually not going to be part of
our final dashboard. But the slicer that's
attached to it and the chart are going to be used
by our dashboard. Now I want to add
a slicer to this. So I'm going to come
up to the analyze. Insert slicer. And I'm going to select date,
time, year, and click OK. Now I'm going to format this
a little bit, change the size. And I'm anticipating that later
I'm going to add more years. So I'm going to come
up to Columns and say, two, three columns,
resize it a bit. And now of course we
can use our slicer to select only a particular
year, filtering our data set. And our pivot table
shows only 2016. We can un-filter it. Now we want to
build a line chart. Let's go up to Insert
over to the chart group. And I'm going to
select the 2D line. Now I'm going to drag the
chart over here, right click. I'm going to say Hide All
Field Buttons on the chart. I'm going to click on
the legend and delete. Click on the title I'm
just going to type revenue and Enter. Now we can see of
course this is a chart attached to our pivot table. So when we filter it, instantly
the chart is filtered also. All right, now we want to
make sure and come over to our pivot table. And we're going to
actually attach this slicer to four different pivot tables. So we want to name
each pivot table, right click the pivot table. Pivot Table Options. Up at the top I'm going to
name it, MonthPivot and Enter. Now we have our
two objects slicer. I'm going to hold
Control and click on our slicer and our chart. Control-X to cut. I'm going to come
over to the dashboard and Control-V Now all we're
doing is loading up our two objects, the slicer
and our line chart for revenue over month and
year on the dashboard sheet. Later we'll create a
couple more visualizations and bring them to this sheet. Now I'm going to control and
roll the wheel on my mouse. Now we need to create our
second and third pivot table on a new sheet. And I need to access
the data model. Now I could go back
to the data model and click that
pivot table button. But watch this. This is an amazing
new option in 2016. Insert Pivot Table, or we
use our keyboard Alt-N-V. And there it is. It's even the default because
we have stuff in our data model. Now in earlier versions
this option wasn't there. You'd actually have
to click here and go to Choose Connections. But I love that. Now we definitely want
it on a new worksheet. So use this workbooks
data model, new worksheet, click OK or hit Enter. Now I'm immediately going
to double click and call this sheet
SalesRepReport and Enter. Now here's the same
data model table. I'm going to drag
SalesRep down to row. Instantly I get a unique list. And watch this. This is amazing. There's our formula. I'm going to drag
it down to Values. Instantly it is the sum
DAX formula and our number formatting. Now I will change this
one Design, Report Layout, Show in Tabular. Actually that doesn't matter so
much because this pivot table won't show up in our Dashboard. But I like to be explicit
and have the field name. Now we need to
create a bar chart. Insert over to the column. And down here 2D Bar. We can drag this
over to the side. Drag the pivot table,
or PivotChart Fields list off to the side. Right click Hide
All Field buttons. Delete the legend. Delete the horizontal axis. Come up to the green plus
I want to say data labels. Now I'm going to click on
the data labels, Control-1. I'm going to move this one over
here, just for the time being. I want to say inside end. Now with those labels
still selected, I'm going to go to
Home and choose Font. There's a bunch of different
places we can get font. I'm going to choose white. Now I want to click
on the columns and over here I could see
already have my gap width. I want to highlight
the gap width. Now this is categorical data. So we cannot reduce it to zero. That's only for histograms
where we have an upper and lower limit and
nothing in between. But I do want to
decrease the gap width, so I'm going to
type 50 and Enter. Now I'm going to click on the
vertical grid lines, Delete. Now I would like to attach
this chart and this pivot table to that slicer. But any time I
change the slicer, there's not going to
be a label up here that says sales rep,
revenue, and the year. Because our year is
not in the pivot table. So we're going to
learn a little trick. Now remember the month
report sheet and sales rep report sheet are not
part of our final dashboard. They are just sheets
behind the scenes, generating the summarized
data for our visualization. So I'm going to create a
second pivot table right here and only add the year field,
Alt-N-V to open up our Create Pivot Table dialogue box. There it is. Use this Workbooks Data Model,
Existing Sheet E1, Click OK. Now here's our field list. Watch this, I'm going
to simply drag the year down to the filter area. Now right now, I could
use the filter drop down and select various years. But that's not
what I want to do. I actually want to attach
both of these pivot tables to that slicer. So I'm very carefully going
to name each one of these. Right click, Pivot
Table Options. Up at the top, I'm going to call
this SalesRepPivot and Enter. I'm going to click in this one,
right click Pivot Table Options and we'll call this
YearPivot, Enter. Now I want to go
over to the dashboard because remember our
slicer is over here. Right click the slicer. Report Connections. And now we have named
our pivot tables. [? Right now ?] I'm
going to check them all. Click OK. Now I'm going to select 17. Sure enough, of course
it governed this. But it's also governing
what happens over in this pivot table, In this
pivot table, and in this chart. Now the problem, of course,
is for this visualization I actually want a chart
title that includes the year. So right in the
cells over here, I'm going to type a label,
ChartTitle colon, and then we'll make
our formula that we're going to use in our chart as
our chart title right here. Equals and then
double quotes sales revenue space dash and
double quotes ampersand. And I'm going to adjoin
it to this pivot table. Now think about this. This Formula is linked
to that pivot table, which is linked to the slicer. So when I hit Enter, any time
I change the slicer, which will be on our dashboard right? 2016. That pivot table changes. That label changes. Now we can connect
this chart title to that cell solid line
around the chart title equal sign shoots me
up to the formula bar. And I click on
cell E5 and Enter. Now the slicer is going
to govern everything. This is our finished
visualization from this pivot table. All of this is just sitting
on this SalesRepReport sheet, generating the data and
visualizations that we want. All right I'm going
to click Control-X, come over the dashboard
sheet and over to the side. Control-V, And we're
just loading things up. We still have one more
cross-tabulated pivot table we want to create. Alt-N-V. Use this workbooks
data model, New Worksheet, click OK or hit Enter. I'm immediately going
to come down and call this CrossTabReport and Enter. Now our pivot table
field list is right here. I'm going to Expand and I want
to drag product down to Rows, region down to Columns. And there is our
DAX sum function with number formatting. And I drag it down to values. And look at that. There is our pivot table. I immediately want to go
up to Design, Report Layout Show in Outline. I actually want the
field names, but I don't want any of the borders. Now I want to drag
the year field. So right here, I want to
drag it down to the filter. We're going to do
the same trick. We're going to name
this pivot table, which of course is part of that filter
and connected to the slicer. So then this pivot
table will also be governed by that slicer. We need to name this. Right click, Pivot
Table Options. I'm going to call it
CrossTabPivot and Enter. Now I'm going to go over to the
dashboard and the slicer I'm going to right click,
Report Connections, and check that cross tab pivot. Click OK. And so now when we
go back over here, it should be sliced or
filtered by that slicer 2016. Now our last step
for this cross tab is to add conditional formatting
for the top 20 values. I'm going to click
in a single cell. Home, Conditional Formatting,
Top Bottom Rules, Top 10. Now let's change this to 20. And I'm going to add
some custom formatting, try to make it blue like
the rest of the dashboard. Fill, I'm going to try something
like that blue right there. I'm going to change
the font to bold. Click OK. Click OK. Now it only appears in one cell,
but that little smart tag only appears when you apply
conditional formatting in a pivot table. So I click on that, and I can
say Selected Cells, all cells showing revenue I want
all cells showing revenue values for product and region. And instantly, the top
20 are highlighted. When we slice this, that
conditional formatting will change. Now I want to leave this
pivot table over here, but I do want to copy and
paste it onto our dashboard. So we're going to learn
how to copy in this case a pivot table in some cells. But you could highlight
anything you want in Excel. Cells, charts, whatever. And when you copy it
and go over to paste it, instead of using
Control-V, I'm going to go up to clipboard paste
drop down or right down here at the bottom, it
says Linked Picture. That is amazing. This is just a picture here. Now notice when I move
it it's got translucent. So I want to add
some white fill. Click on that picture, Control-1
to open up the Task pane. I'm going to move the
Task pane over here. I'm going to say, Fill Solid,
and change it to white. Now we can arrange and
finish our dashboard. What I'd like to do is
highlight a bunch of cells more than I'm going
to actually need. And add a background color. I'm going to click
the light blue here. Click off to the side. Now we can arrange-- and I see
one little problem right here. I think I'm going to go back
over since this is the picture, I'm going to go back
over to that sheet. I actually want those
cells right there to be the same color
as these cells. So I'm going to go back
over to cross tab report. I'm first going to turn off the
grid lines, View Grid Lines. Then I'm going to highlight. And if I can remember
what color I used, that's going to highlight those. Remember, this is just our
input for our dashboard. Home, Fill Color. And sure enough, there it
is, the actual fill color I just used. So boop. And now if I go back
over here, of course because it is a linked
picture, it updates. Now I'd like to make both
charts the same size. So I'm going to
click on one of them, go up to Pivot
Chart Tools, Format. And over here I'm looking
at size three inch. and I'm going to change this
one to five inch and Enter. Now I'm going to click
on the second chart and do the same thing,
five inch and Enter. Now if I click on
both of the charts, I could come up to
Format and Align. I guess I'm going to Align Top. Now I'm going to
move this picture. Now with this picture selected,
I'm going to select this chart and say drawing tools
Align and let's say Left. I'm going to move this
chart a little bit closer. I should have aligned it first. Align and we'll do Top. I'm going to move the
edge right so it's almost even, move
the slicer over here, maybe right in the middle. And I'm going to add
[? Boom ?] Incorporated period, Control-Enter. Change the font size
to 20, Control-B. So we can control and roll our wheel. Close these task panes here. Highlight and we can go
to View Zoom to Selection, or-- I do this all
the time-- Alt-W-G. I'm going to click
somewhere down below. And there is our slicer. We can slice and dice by year. And all of our
visualizations are updating. Now I'm going to Control-S.
And here's the big drum roll. Remember, one of the
hallmarks of the dashboard is when we get new data
available, all of this should update. So we're going to go back
over to Windows Explorer. And there's our
Video17ImportTextFiles folder. Inside was the start and if
we look inside the start, we have those two years. And by the way, I kept
this kind of simple, so we just had three files. You could have daily files,
monthly files, quarterly, or whatever. But here's our new data we
just got as a text file. I'm going to copy, double click
inside of start and Control-V. Now remember, power query is
looking at that Start folder. So anything inside
there will get run through Power Query, all of the
steps of removing the columns, organizing the data
into the data model, and flowing into our dashboard,
will hopefully update. Now I'm going to use the
keyboard, Control-Alt-F5. If you want the location, you
could say data, refresh all. Also a lot of times, I
just have the query open. You could say show
queries, you can just right click and refresh that query. But I'm going to
use the keyboard. Control-Alt-F5. And just like that, there
is our updated dashboard. When I click on 2018,
instantly everything updates. It looks like we have a couple
Top 20 in Africa region, 2017, one over here 2016. Now I can un-filter all of this. And there it is. Now one last thing. We're looking down here
and we notice the Try Fly. We did not want that as
part of our analysis. That's no problem. Here is the original
query transaction table. Right click Edit. I'm coming back
here, and I'm simply going to come and filter
for the original query. There it is, Try Fly. Click OK. And now I'm going
to close and load. And I can just
click this button. Because it already has the
location into the data model. So when I close and load, it is
gone from all of our analysis. And we can slice and dice. Wow, that was an amazing video
about building a dashboard. Here's the finished product. But back over on 1, 2,
3 different sheet tabs, we have our month
and year pivot table. We had our SalesRep pivot table. Our pivot table to get the year
for our chart title formula, and then back over here CrossTab
with conditional formatting. Now in our next
video, we'll talk more about cleaning and
transforming data in Excel, not just with Power
Query, but with some other amazing features. All right we'll
see you next video.
