Welcome to Highline Excel
2016 class video number 20. Hey, if you want to download
this Excel file Business 218 Video 20 Start, or the
finished file, or the PDF file, click on the link
below the video. Wow, we have an
amazing video here. We've been talking about
cleaning and transforming data. And of course, now
we're gonna talk about Power Query known as get
and transform in Excel 2016. And in this video,
we're going to see how to clean, and transform, and
import data from multiple Excel files where each Excel
file has one sheet each. And then, we're going to create
a dashboard with a pivot table and see the show values
as a feature for creating calculations in a pivot table. Now we want to start
off by going and looking at the actual Excel files
that we're going to import. We're going to start
with three cities. And if we open one of these,
it's an Excel file, of course. And notice there is a sheet. It says Seattle. And here is our data. Date, sales rep, store ID. If I Control down
arrow, it looks like there's 200,000 rows
of data here for the Seattle store. Not only that, but because
we're going to be importing data from Excel, and
we have many files we're going to use the From
file From folder feature. Now earlier in the class,
we used text files. And this feature works almost
seamlessly with a couple clicks if you have text files. But the difference here
is these are Excel files. Excel files do not
hold just text. They hold lots of different
objects, like a sheet. Like, over here,
there's a second sheet. Right here, if I
highlight this range, you can see there's
a defined name. If I go over and open up one
of these other Excel files, Tacoma. Yes, it only looks like
there's a single sheet here. But if I right-click,
notice I can point to Unhide, which means
there are some hidden sheets. Over in the Portland
file, we not only have our Portland sheet
with our data that we want, but we have another sheet. And there's an Excel table. So the point is there is lots
of objects in an Excel file. So when we tell Power Query to
import all the different Excel files, it's going to take
a bunch of extra steps. And actually, we're
going to get to see a brand new function called
Excel.workbook in power query. And finally, we're going
to look at our end result. We're going to go over here. Here's the dashboard
we want to create. We need to group by month. Then, we need total
sales, percent of total, the amount of change
from each period. Percentage change. A running total by month, and
the percentage running total. And of course, our
slicers so we can click and slice to show any
particular city or year. All right, now we're
in our start file here. And we notice we
only have one sheet. But guess what? We're going to import a lot
of data into the data model. So we want to go to Data. Get & Transform, which
is actually Power Query. New query from file from folder. And just as we did
earlier in the class, we click the Browse button. And we need to navigate. Now when you download this file
here, you have to unzip it. And there are a
bunch of extra files directly in the Video 23
Excel files to import. But we want to
click on the Start. But that Start file right
there has those three files we're going to clean,
transform, import, and then build our dashboard. All right, I'm
going to click OK. And when I click OK, I'm telling
Power Query to import anything in that start folder. Click OK. Click OK. If you see this intermediate
step, click Edit. Now in the Power
Query Editor, we have a bunch of information
about those three files. But we want to start off
by giving it a good name. And remember, this name
will be the query name. So when we have to edit it
later, we can come back. It will also be the table
name in PowerPivot data model. And we want to give
it a good name. Something like City
Excel Data and Enter. Now here is our three files. And there's some columns with
information about those files in the folder. The first thing is that
our city name is going to come from the file name. So I need to extract city name. And secondly, from the
extension column here, we notice it's .xlsx. But I actually want to protect
with the filter feature against any other
types of files, like if there's a Word document
or a text file or something. I only want to import .xlsx. Now earlier in the
class, we assumed that our folder would only
have a certain file type, which is usually a safe assumption. But here, let's
see how to do this. I want to make sure
that I only get .xlsx. I'm going to click the
drop-down text filters. And I want to say the file
extension contains .xlsx. That way, it will only
import if it sees .xlsx. Click OK. Now we need to
extract city name. I'm going to notice the pattern. It's always two characters
I want to get rid of. And I always want
to get rid of .xlsx. I'm going to click
on the Name column. And first, I'm actually
going to split it based on a fixed width
of two characters. So I come up to split
by number of characters. I'm going to say two once
as far left as possible. When I click OK, it
splits the column. Now I need to clean up
this name to column. I'm going to choose to
replace .xlsx with nothing. So I click Replace values. Type .xlsx. And I'm going to
replace it with nothing. Click OK. Now I have two columns,
the content and the name. None of the other
columns I need. So with name two
selected, I'm going to hold Control and
click on Content. Right-click. Remove other columns. Now we mentioned that the
Excel file with lots of objects is much different
than a text file. So that means we cannot use that
double downward pointing arrow. That's if we have text files. We're actually going to have
to add a new column and use the Excel .workbook function to
get the right objects from this content column. So we come up to Add Columns. Add Custom Column. We're going to name this column
something smart like Get Excel Data. Now I can create a custom column
formula using Excel.Workbook. Now this is the first time we've
seen a function in Power Query also called the m language. This is a special
function that's going to work inside
a Power Query. It is case sensitive. And you need that dot. But there it is, Excel.Workbook. And I'm going to double click
on the content over here. Notice there's our
square brackets to indicate that that's
a column or a field. Close parentheses. And that's all we need. This will actually
create a new column with all the different objects
that we can select from. I'm going to click OK. And there it is. It says table will
actually expand it in a second to get to
the different objects. We no longer need content. Right-click remove. Now I have a double
sideways pointing arrow. I'm going to click
this to expand. It will open up a drop-down. Different objects, name,
data, item, kind, hidden. We do not want to have use
original column as prefix checked. That just means it would use
this name as column header names. And I want all of these. So now I click OK, which will
add a bunch of extra columns. Now hidden, it just
says true or false. We saw that there was one
hidden sheet over there. We don't need to worry
about this column. Kind, we do need to worry about. We need to filter this because
I do not want define names. And I do not want tables. So I want a filter to
only give me a sheet. So when I click the
drop-down, I can come unselect all and select sheet. Now you remember that some of
the sheets had names and some didn't. This just gives
me all the sheets. When I click OK, that
column is filtered. And you can see all of
our steps over here. Now I'm going to come
to the item column. And these are our
different sheet names. Now I would like Seattle,
Tacoma, and Portland. And later, if I
have sheets called SF for San Francisco or OAK for
Oakland, I want those sheets. So I actually want to tell this
column to do a text filter. And I want to say I
only want files that do not contain the word sheet. That means if it has the
default sheet name sheet, it will not be imported. If it has the proper city
name, then, of course, it will be imported. I'm going to click OK. And there we have just our
sheets for Seattle, Tacoma, and Portland. Now if we always
have the actual city name in proper form
for a sheet name, then we could use this column. Remember, we had to
work hard over here in the name to column. This was the original column
that had the file name. But because some of our
sheets later we'll see don't have a proper name, we're
not going to use this column. Now all we need
is name 2 in data. So I'm going to click on name 2. Hold Control. Click on Data. Right-click Remove
Other Columns. Now we have city in our data. And now we can use
our double sideways pointing arrows to expand. Remember, we had three columns? Column 1, 2, 3 will be
date, sales, and store ID. I don't want this checked. I click OK. And now we finally are
getting down to the data. Now just like in our earlier
example with text files, there are field names here
in the first Excel sheet. But there's also field
names down below. Well we're going to
promote the first row by coming to the upper left hand
corner, clicking the drop-down, and say first row as headers. That still means we have trouble
down below and in future files. So I want to actually
pick whichever column has the fewest unique
records, not including this one because this one
will have actual city names down there in the record
that contains the field name. So I'm not using this column. Store ID has the
fewest unique records. So I'm going to
click the filter. It's a big data set. So I'm definitely going
to read the message. List maybe incomplete. Load more. And now when I scroll
down, there it is. Down below is a
rogue field name. When I filter out the
field name store ID, the whole record is removed. That means in any data sets we
have now or future data sets, it will remove store
ID and the field names. Click OK. Now I'm going to come up here,
and double click, and call this city. Enter. Now there is one step
that we are going to forget to do right here. And it will cause trouble later. But that will be our prompt to
come back and edit this and see that everything
updates beautifully when we use Power Query. All right, so we're
thinking we're done. We have our name. We have all of our steps. I'm going to come up to Home. We're definitely
going to click Close and Load To because we don't
want to load this into an Excel table or onto a sheet. I say only create connection. Add this to the data model. And now when I click
Load, there it is. 500,000 rows of data. Now that could have
fit into Excel. But we're going to get
future data updates. And certainly, if we store
it in the data model, it is a much smaller file size. All right, I want to go look. I'm going to go to PowerPivot
over to Manage button and Data Model. I want to look at our data set. And there we have
1, 2, 3, 4 columns. There is the table name, which
is also Power Query name. There's how many
records we have. Now I want to go back
over to Excel PowerPivot. We are going to create
a measure to add. So I'm going to say
measures, new measure. This is the DAX
formula language. Of course, as we've seen a
couple times in the class, the advantage is we
build the formula here. Add our number
formatting, and it will appear in the field list. And we can drag and drop. Now the table name
up here is the table that the DAX measure
will appear in. The name of the new DAX measure
is going to be total sales tab. I'm going to give
it a description. DAX Sum function to add
total sales from sales field and add number formatting. Now I can click after
the equal sign SUM tab. Down arrow and check that out. There is the table name. And in square bracket,
our sales column. Be sure to put a
closed parentheses. Now we come down here. And you can pick which
number formatting you want. I want currency
zero decimal places. And now when I click OK,
this measure will be created. Now I want to go over to
our data model window. Alt tab, and there is a problem. So this is something
that happens. We're in a hurry. We've done our Power
Query to import it. We create our DAX measure. And there's an error. Well we can read. If we click right here,
it says semantic error. The function sum takes an
argument that evaluates to numbers or dates and cannot
work with values of type string. Now a text string means that
there aren't text values. We can even look up in
our data model table. There's our visual cue. All the way back to the
very first video number one in this series, we
talked about how important that visual cue is. So there it is. We have to go back
to Power Query and change the data type
to some sort of number. Now we can fix it over here. But I want to fix it
over in Power Query so any time we import it,
it's all working fine. Now we go over to Excel. If you don't have your
Workbook Query window open, you go to Data. Get in transform. Show queries. I'm going to come over
to City Excel Data. Right-click edit. And sure enough,
we need to click on each one of the columns. Go up to Data Type. Text is fine. I'm clicking on date. Any is not OK. If we tried to group,
that it would not work. I'm going to say date. Sales, how about instead
of any, let's do decimal. Store ID, I'm going to say text. And now when I click Close
and Load because we've already loaded it, it will load right
back into the data model. And now when we come back to
our data model, there it is. It's working fine. Now let's create a pivot
table on a new sheet. I'm going to click
Pivot Table, new sheet. Click OK. I'm immediately going to come
down and call this something like report and Enter. Now let's drag this over here. We could always tell that the
table is from the data model. It's got that dark
line at the top. Expand there is our
DAX measure for adding. Now the first thing I want
to do is group by a month. This is a new feature for
2016 Excel in the data model. When I drag the
date field, which are serial number dates down to
the row area, it takes a while. But there it is. It added a bunch
of new fields here. We can see over in
our pivot table. When we go over
to our data model, we can see it's added a
bunch of extra columns. Now of course, if you
had a big data set, you'd want to build a calendar
table and use relationships. But for our data set,
that is pretty convenient. And it is an amazing new
feature in Excel 2016. Now I want to remove date. I'm going to click and drag. I want to remove quarter. Click and drag. I don't even want
year right now. Just like that,
I have my months. I'm immediately going
to fix row labels, design, report layout, show
in tabular, or outline. I'm going to say outline because
I don't want any gray lines. Go to our table. Drag total sales, one. And get this, I'm going to
drag it down a second time. Now this will be the
first time that we've seen show values as in this class. We want to add those
extra calculations we saw at the beginning of the video. I want each one of these
amounts as a percentage of the overall total. Right-click in its
value field settings. You can change everything here. The name, the number formatting,
the function, or show values as. Now I'm going to say
percent of grand total. I also want to make sure
and give it a good name. Percent of total. Now because we're doing a
percentage to show values as, we'll automatically
add percentage number formatting so we don't need to
worry about number formatting. When I click OK, that
is pretty amazing. Now I'm going to drag total
sales down one and two more times. So now we have four columns. And I want the actual
amount of the change. That means from each month. January to February
to March, I want to say the amount of change
from the previous month. Then, I want to see
percent of change. Right-click Value
Field Settings. Here are show values as, and
I want to say scroll down. Difference from will
show us the change. Now it needs to
know the base field. We only have one
in the row area. And it needs to know do we want
it from the previous, the next, or sometimes you want to
see the amount of the change of a certain base. For example, January. But we want previous. We want to make sure I
can give this a good name. I'm going to call it Change. And click OK. Check that out. That is amazing. Totally different
calculations than we've done so far in this
class in a pivot table. Right-click Value
Field Settings. I'm going to scroll all the
way down to percent difference from. Base field, of course, is fine. We want from previous. And we want to give this a
good name like Percent Change and Enter. Absolutely amazing. Now lets drag it two more times. One. Two. We can drag down the
pivot table field list if we want to see them all. And now we want to
do a running total. So it's a cumulative
total moving forward. So that means when we get
all the way to December, it will be the
grand overall total. Right-click Value
Field Settings. Drop down. Let's see. Running total in. Base field is fine. And we're going to call
this running total. Now we don't need to worry
about dollar sign, currency type, number
format, because it's acting on that DAX measure. Click OK. Then finally, we want to
come to the last column. Right-click value field settings
all the way down to percent running total. Now we're going to
give it a good name. Something like percent
running total and enter. Now I want to add
some slicers up above. I'm actually going to
select the row header three. Maybe all the way down to seven. Right-click Insert. Click somewhere in
the pivot table. Pivot Table Tools analyze. Insert slicer. Let's say year and city. Click OK. As we saw on our
dashboard video, we can format these
however we want. Here is our slicer tool options. I'm going to say please
give me three columns. Now I'm going to set the
height to two inches and Enter. Not two inches. I'm going to set it to one
inch and Enter and expand. I'm anticipating that we'll
have more cities later. And how about this one, too. I'm going to add it on to rows. So I'm going to say
leave it like that. Now let's add a little
bit of formatting here. I'm going to click
inside the pivot table. Design drop down. And we talked about a custom
pivot table style back in video number three. But I'm just going
to pick, let's say, pick this one right
here, medium 13. Click on the slicer. I'm going to add similar color. Click in the slicer
similar color. Let's format this. I'm going to say one inch Enter. Click on both of
them holding Control. Align top. And now I'm going to
go to View grid lines. Turn them off. OK, so there we have our
sliceable, pivot table report. I'm going to right-click. I don't like the way the
columns are changing. So right-click
pivot table options. And down here, Auto-Fit
Column what's on update. We'll uncheck that. Click OK. 2015 Tacoma, Seattle, Portland. Now let's go back, and I want
to look in Windows Explorer. I'm going to go back
over to Windows Explorer. And I'm looking just inside a
video 23 Excel files import. There's a bunch of extra files. And I want to test our theory
to see if our filter to include only .xlsx will work. If I double-click the
Oakland file to open, if I look down here,
notice there's Oak. And that's not the actual
word we wanted in our column for city name. Also, in our San Francisco
one, I think it comes in as SF. So that is the reason that we,
from our original three files, we chose to get the city
name from here because that is the consistent source
for our city name. All right, I'm going to
highlight all of these. Control C double-click
Control V to paste them here. Now remember we originally
pointed Power Query to that start folder. So now it will try to
import all of these. Let's go back over to Excel. Now I'm going to use the
keyboard, which is the data. Refresh all. I only have this data
model and this pivot table. So that's where I'm
going to use that. Sometimes if you have
lots of things to refresh, you don't want to do that. I'm going to use the
keyboard Control Alt F5. And now it is working. And just like that,
it has refreshed. You can see over here
in our Workbook Queries, it looks like
810,000 rows of data. Now I can slice on San
Francisco and onto Oakland. It looks like I need to fix my
final report a little bit here. Now I can slice and
dice however all I want. And it is certainly amazing. When we clicked Refresh,
it went back to that folder and got all of the latest data. All right, so in this video,
we saw for the first time show values as. That powerful calculating
feature inside of a pivot table. We made our slicers in
our little dashboard. And of course, we saw
data from file from folder to import multiple Excel files. All right, next video, we'll
have a little bit more fun importing data from
different Excel sheets with Power Query and
building reports. All right, we'll
see you next video.
