Welcome to Highline BI348,
class video number 29. And if you want to
download this file, BI348 Chapter02.5 Import01
TextFiles start file or the finish file,
and don't forget, for these examples
in this chapter, here's the links at the site. You got to download that
zipped folder that has all the files we're importing. All right. Now, we want to start off by
going and looking at our files we want to import. Now, last video, we
imported these text files. Here we have text files, but
they're comma-separated values. And if I open this,
it looks like Excel, but it's really
comma-separated values. And if I Control down a
little, there's a lot of data. There's 250,000 rows
in this one workbook. So one of the main things
we're going to do in this video is see how easy it is to
get Power Query to import these in just a couple clicks. But we're not going to
import it into Excel, because if we do and I want
to go take a look at the file size, here's an
example of file size. If we imported this and
put it into a sheet, it would be 25 megabytes. If we instead of importing it
into Excel, use Power Query and do only a connection and
dump it into the data model, we'll get something close
to about two megabytes. All right. So we want to import
these using Power Query. I'm going to go over
to my Start file. And actually, this
Start file here, we actually used this last
video to import gray data and build a dashboard. But here on sales report, we're
going to go up to Power Query. And we're going to do the same
thing, From File, From Folder. We're going to
browse to our folder, and when you download, you
download Import-01 Tech CSV files, you've got to
unzip it, and then inside there's a Start file. Now, what we're telling
Power Query right now is to always look
at this folder. So Power Query will try
to import everything. We will filter out
what we don't want. When I click OK, click OK, the
Power Query editor comes up. I'm immediately going
to come over and give it a smart name, Import CSV
Sales Data, and Enter. Now, just as we
did last video, I'm going to come to the
extension column. When I click on the filter,
it gives me a unique list. And this time, I
don't want the TXT. So now, I'm telling Power Query
to always look at that folder and always bring
in extension .csv. Right now, there's
only three, right? But later, there'll be
more, and Power Query will know what to do with them. So I click OK. We filtered. Now I come over to Content. Right-click Remove
Other Columns. Now, because these are text
files, it's easy to expand. We see our double
downward pointing arrows, we click, and instantly,
it looks like we got just what we wanted. We look over here, just
like we did last video, and there's a bunch
of cool steps, including taking the
actual text field names and promoting them as headers. Power Query did all
of that, including changing any data types. If we click here, we
can see under Data Type, it's a whole number. Over for sales, it's
a decimal number. Right here, that's the important
one to look for, date and text. Now, any time you import text
files, remember, the first file had field names that were
promoted to column headers or fields. But there's some
other column headers down there that did
not get promoted and they're still there. One of the tricks we can
do is to pick the column with the fewest number of
unique records, and it is City. Click the filter. Because it's a big data set,
you got to click on Load More, so it will look
through that column and try and find all of the
items for a unique list. And sure enough, City, somewhere
way down at the bottom, there's an entire record
filled with field names. When I uncheck and filter
City on this column, it will properly
remove the field names from the records further
down in our data set. So when I click OK, boom,
and there's that last filter. We have our name, our steps. By the way, we didn't
look at it last video. You can come over to
View, and Advanced Editor, and there's all the code. M is the name of this code. I'm going to click Done,
Home, Close and Load. That will load it to the sheet. I want power over where it goes,
so I say Close and Load To. Especially now, I do not
want to put it into sheet, and I do not want it in a table. I'm saying Only
Create a Connection. And now I check, Add
This to the Data Model. Now we'll go over just a
moment and look at a PowerPoint slide or two about
what the data model is, but it is a behind-the-scenes
columnar database, which is super efficient
at storing data and doing analytics. When I click Load,
and there it is. Import CSV sales data. So it's like almost
500,000 rows of data. Now, there's nothing
in this sheet. That's in that
columnar database. Now, I want to briefly
go look at PowerPoint. What is the data
model in Excel 2013? It's a behind-the-scenes
in-memory columnar database that allows you to import large
data sets, millions of rows, that would not fit
in an Excel sheet. The data model stores the
imported data, not an Excel sheet or a table, but in an
in-memory columnar database. The bottom line is
this columnar database is efficient at storing
large amounts of data. It can dramatically
reduce the file size, as we talked about earlier. Now, once we import
into the data model, we can access the data model
through a pivot table dialogue box. Now, that's what we're
going to do this week. Next week, we'll look
at what most people do. Most people use the data model
in conjunction with PowerPivot, and that allows you to build
relationships between tables and use a whole new formula
language called DAX formula language. Now, I want to click
on the next slide. There's a bunch of synonyms,
PowerPivot database, columnar database, data model, Excel
data model, PowerPivot xVelocity engine. So if you're
reading, you may see any one of these as a synonym
for our data model or columnar database. Now, on this third
slide, this is slide 17, this is in essence what
the columnar database does when it stores the data. It actually takes a data set
with field names and records and it takes each field,
stores it in a separate column with only a unique list. Then it builds a type of map
of how these unique lists are related to the original
transactional data set. Now, let's go back over
to our Excel workbook. Now, notice that our query
here has about 500,000 records. It would fit on a sheet,
but again, the file size would be so big. So here, this would fit, but
we're using the data model simply to store it and have a
dramatically smaller file size. Now, how do we access it? No problem. Insert Pivot Table, or
our keyboard, Alt-N-V. And we want to use an
external data source. And it's not that it's
outside somewhere. It actually is connected
to this Excel workbook. It's just external
to the sheets. It's that in-memory
columnar database that's really attached
behind-the-scenes to this Excel workbook. I click Choose a Connection. I'm going to go over to Tables. I'm going to select
Import CSV Sales Data. That's our method of
getting at that data model and making a pivot
table from it. When I click Open,
and OK, just like that, I get the fields
from that table. We want Store ID Down to Rows. Sales over to values. Right-click Number Formatting,
something like currency, no decimals. Click OK. We need a slicer, so I'm
going to go up to Analyze. Insert Slicer. I want City, click OK. That's not a good label. Click there and Design, Report
Layout, Show in Tabular. Now I want to make
a column chart for this categorical
data, this store ID. Insert Column 2D. Let's see, that's chart junk. I could keep these
here, Right-Click, I'm going to hide
all field buttons. I'm going to click on the chart
title with that solid line. I'm not going to link it to the
cell, I'm just going to type. And click Enter. Now I can close
these tasks panes, and all we're going
to do is that for now. We can certainly filter,
but let's take a look. And in fact, I'm
going to anticipate. I'm going to come up to
Slicer, say two columns, and then pull this
over to the side. All right. So now, what do we want to do? We want to save this and
go over to our files. There's the original CSVs. But when you download this,
you download the Import-01, and there's the Start and New. So we're going to grab the new
one, Seattle and Tacoma new CD data. Control-C to copy, and
Start file, Control-V. Now, when we refresh
over in Excel, remember, the query says look at this
folder and get anything with .csv. So when I come over
here, let's try this. The keyboard is Control-Alt-F5. And sure enough, look at that,
we got Seattle and Tacoma. No data set here,
just our pivot table. So when I filter on Seattle,
there's the new data. Look at that. All right. I'm going to un-filter. So in this video, we saw
how to import multiple CSV comma-separated value files
using Power Query, create a basic report, and then
sure enough, when we update and that folder has new
files, everything updates. Now, in our next
video, we'll see how to import not text files,
but actual Excel files. All right, we'll
see you next video.
