Welcome to Highline Excel 2016 class video, number 21.
If you want to download this file, Busn218-Video21Start
or the finished file or the PDF files and follow along,
click on the link below the video.
Hey, in this video, we're still talking
about Power Query, known as Get and Transform in Excel 2016.
Last video, we saw how to import multiple Excel workbooks,
put in that example, we had one sheet
from each workbook we wanted.
And in this video, we want multiple sheets.
Not only that, but we're going to have
to create a City column in our final data set
and get the city names from the Excel file names.
And each sheet in each Excel workbook that has data
has the sales rep name, and we need
to create a Sales Rep column and get
the names for the different sales reps
from the worksheet names.
Now last video, we saw how to use Excel.Workbook,
but we didn't use the second argument, which is true,
and it actually has to be in lowercase, not uppercase.
This argument in the Excel.Workbook function
will not only get all the content,
but it will also by putting lowercase
true will promote the headers.
If you remember last video, we had to do it manually.
All right.
Let's see how to do this, but first,
let's go look at the files we have.
Here they are-- three Excel files,
and each file represents sales from different cities, so
the store in Grainville, the store in Kipsat,
and the store in Othello.
So we will have to extract those names
and repeat those names in a City column.
Let's go look at one of these files.
In each file, there will be Date, Product,
and Sales columns, but look down here--
Gardenia, SheliaDawn, and Min.
Each different sheet has different sales records
for each one of the different sales reps,
so there's sales rep Min, SheliaDawn, and Gardenia.
Now this Excel file, of course, has the first row
as field names, so we're going to see
how to use that lowercase true in Excel.Workbook
to automatically promote these.
Let's go look at one more file, Kipsat.
So again, these field names, by using lowercase true,
will be promoted, and each one of the sheets--
Timmy, Chin, and Tyrone-- will have
to extract those sheet names and repeat them
for the particular sales rep.
So let's close this, go back to our Start file.
Now we need to import those three Excel files.
We go to Data, Get and Transform,
New Query, From File, From Folder.
We want to browse, so I'm going to click the Browse button.
And when you download your files,
there's a zipped folder called Video21-3ExcelWorkbooks.
When you unzip it, there it is.
We're telling Power Query to import anything in that folder.
I'm going to click OK.
Click OK.
There's the Edit button.
I'm going to click this.
You might not see that intermediate step,
depending on what updated version of Power Query you
have.
Now there's all the information about the files.
We immediately want to come over and give this
a smart name, something like CitySalesRepImport, and Enter.
Now we have a bunch of columns with information
about the files.
Last video, we actually filtered to say, contains .xlsx,
but this is a one-time import, and we don't have any other
files besides .xlsx, so we don't need that column.
We do need to click on Name and extract the city
name from each one of these file names.
I'm simply going to notice the pattern stored
on XLSX, stored on XLSX.
We can simply search and replace that with nothing,
so we highlight it, Home, Transform,
and there's Replace Values.
Find what?
Store.xlsx.
I'm going to replace it with nothing.
Click OK.
There we have city name for each one of our files.
Now we don't need any of these other files,
so Name is selected.
I hold Shift.
Click on Content.
We need these two columns, so I right click.
Remove other columns.
There's our content, but we click that little double arrow
only when we have text files.
We have Excel files with lots of different objects,
so we go to Add Column, Add Custom Column.
And I'm going to call this new column GetExcelData, Tab.
And just like last video, we'll use this smartly named
function, Excel.Workbook(.
Now we're going to double click Content,
and there it is in square brackets.
That's this column over here, Content.
And now we do comma, and in the second argument,
we type true in lower case, close parentheses.
That true will automatically promote the headers.
Now we have our formula.
We click OK.
There is our tables from our Excel data.
If we click on each one of these, you could see a preview.
Down here, click on each one.
We see a preview, including those promoted field names
at the top of each data set.
Now we're going to click this Expand button, because we still
have some objects we need to filter out.
I want to uncheck Use Original Column Name
as prefix, because I don't want that get external data.
There's our different columns that we
can use for filtering out objects like table,
define name, and so on.
I'm going to click OK, and there are all of our columns.
Now hidden column-- last video, we had some hidden objects.
Here, we don't, but we don't need to mess with that.
Kind-- we can see we have sheets.
There's a table.
There's a defined name.
I want to filter this to only include sheets,
so I click Select All, and then Sheet.
Click OK.
So now we filtered only showing the sheets.
Now the Item column tells us the names of certain sheets.
We can see our sales rep names, but there a sheet.
Here's all the sales reps.
So I want to filter out any sheet that
contains the word "sheet," which is the default name.
So I'm going to click the drop-down, Text Filters.
And I want to say, please only keep sheet names
when the sheet name does not contain sheet.
Now hopefully, we don't have any sales reps named Sheet.
We don't, so this will be OK.
Click OK.
And there we've filtered out all of the sheets that
contain the word "sheet."
Now these are going to be our sales rep names.
Here's our data, and here's our city names,
so I'm going to click on City Name, Data, and Item
using the Control key.
Then I right click.
Remove other columns.
Now here's our sales reps.
I'm going to double click Item and call this Sales Rep
and Enter.
For city, I'm going to double click Name, and then type City
and Enter.
Now I can click my Expand button.
Date, Product, and Sales-- those are the promoted field names.
Click OK.
Now we're almost done.
We need to click on City and make
sure Home over here and Data Type, I want it to be text.
Date-- I want the data type to be Date.
Product-- I want to make sure it's text.
Sales-- I'm going to say, hey, please use data type
decimal number, and sales rep will be data type text.
We have all of our steps.
There's our name.
We have our data types.
I'm going to click Close and Load,
which will accept the default, which is please
put this as an Excel table on a new sheet.
I'm going to come down here, double click Sheet,
and call it Data, and Enter.
And there it is.
There's our imported data from those three Excel files.
Over here, we can see our workbook query has 3,533 rows.
Now here's the data.
You can analyze it how you want.
In this video, we saw how to import three Excel files
and automatically promote the headers using
the Excel.Workbook function and second argument, little true.
We saw how to extract city name from the file name and sales
rep name from the sheet tab names.
All right.
We'll see you next video.