[MUSIC PLAYING] Welcome to MSPTDA
video number 14. This is our Power
Pivot intro number two. And guess what? We're going to use Power
Query and Power Pivot. I thought this was
a Power Pivot intro! Well, guess what? Power Query is a
part of Power Pivot. Now, it's not really a part. But in the old days, when
Power Pivot first came out, we used the Power
Pivot user interface to go and import data. But we don't do that anymore. Now we use Power Query to go
and import or extract the data, and then we dump it into the
Power Pivot columnar database, and then build our DAX formulas
and data model pivot tables. So in this video, we
want to see a quick tip about how to import millions
of rows of data into Excel. Now, just like last video,
Power Pivot intro number one, this is a trick
simply for Excel. And the reason it's
so important is this-- we Excel people,
for decades, have been getting text files that
we need to import into Excel. Now, we'll go look
at one of these text files in just a second. But I want you to
notice something. There's 2018. Look at the file size-- 38 megabytes. If I were to import just
this one file, just the sales from 2018, into an Excel
file, the file size would be 30 megabytes. Now, look at this down here. This file is only 8.5 megabytes. And it has all of the text
data imported and appended into a single table, then
loaded to the data model, and we made our
pivot table report. And look at the file size. That is simply amazing. That means the
columnar database is very efficient at
taking lots of data and compressing it,
and then allowing us to make pivot table reports. If we go look at
one of these files, all the files have ISO date,
product, sales rep, units, discount, price, and sales. Now, in this video, we simply
want to take text files and append them
into a single table. So all we're going to use
Power Pivot for is a place to load the data. Then, when we make
our pivot table, we'll check, please use the
table in the data model. Now, this is the
name of the folder. It's a zip folder. You can download
it and unzip it. Also, as we've
talked about earlier in this class, when
we did Power Query, Power Query is case sensitive. So we're going to have
to deal with the fact that some of the file
extensions are capitalized. Some of them are not. We also want to filter out
any files that are not TXT. Last thing is, we have
to know the delimiter. The delimiter separating the
columns in the data is Tab. Now, we're over here
in the Start file. I'm going to go to the sheet PT. Now, we go up to
the Data Ribbon tab, and this is Power Query,
Get and Transform. Now, remember, I
have Office 365. So my icons may look
different than yours. But now we want to go to Get and
Transform, Get Data, From File. And because we have lots
of files in the folder, we want to use From Folder. Now, I'm going to
click on From Folder. Now I click Browse. I point Power Query to
the folder that contains all the files I want to import. Click OK. OK. Now we have a
preview of the files and various attributes
for those files. We do not want to click Combine. I do not want to click Load. I want to click Edit. And when I click Edit, the
Power Query editor opens up. This is the column that
contains the files. There's the file name. Now, extension, we need
to right-click, Transform, and Convert to Lower Case,
because Power Query is case sensitive. We want to filter
now to include text. But I only want one filter
that says .txt, lower case. So I'm going to click
the Filter dropdown. Notice this is just like Excel. We're using our Excel skills. Uncheck everything. Now, if you only
have text files, and you still want
to do this, then you have to go to Text Filters
Equals, and type it out. But because we have
more than one file type, I can use this
method right here. And when I click OK, the
same line of code is written. Now, if I use the Expand
button to append all of these, this column will be included. So because I don't
want that column, I'm going to right-click and
say Remove Other Columns. Now, when we click this
downward-pointing Combine Files button, and
we've seen this before. I'm going to click
and open queries. It will create a bunch
of queries for us, including a function. So I'm going to click the
double downward-pointing arrow. It first wants to know
what the delimiter is. So we're going to say Tab. Click OK. And now it'll build, from
the sample file, some M code. It will convert
it to a function. And then, in the table we
want, it invokes that function and does a number
of other steps. Now, we've already learned
all about the specifics of this process right
here earlier in the class. Now we have some transformations
that we want to do. And the first one is,
these are ISO dates. And as we saw two videos ago,
if we get ISO dates as numbers, we first need to convert
it to text, and then to dates, a two-step process. Now, notice, one of
the automatic steps was to change type. So when I click the
Data Type icon there, I'm first going to
convert it to text. And it's going to be polite. It already has a step,
and it's asking me, do I want to replace it? Yes I do. So I'm going to click Replace. Now, that step contains
that conversion to text. Now we click the Data Type
icon, and convert to date. And very importantly, we do
not want to click Replace, because we need that
previous step there, converting it to text. Then I click Add a New Step. And sure enough, we
have our proper dates. Now, one thing about
importing a lot of data is if we have columns we don't
need, we should delete them. If we're never going to
use units sold, discount, and price-- which we're not,
for this particular Excel workbook-- we
should delete them, because it's extra space
in our columnar database that we don't need to use up. So I'm going to
click on UnitsSold, hold Shift, click on Price,
right-click, Remove Columns. And now we have exactly
the data set we want. We've got to come over here, and
I should have done this first. We're going to name this. All text files, one
table, and Enter. Now, as it turns out, we have
over 3 million rows of data. So if I tried to close and
load by clicking this button, it would try to
load it to a sheet and say that it could not load. So we're going to click the
dropdown, Close and Load To. I do not want table. Even if I come down here and
click Add This to the Data Model, it'll try and do both. I have to click Only
Create a Connection. Add This to the Data Model. And when I click
OK, it will actually build a super efficient,
in RAM memory, columnar database, which
is part of the data model in Power Pivot. So I'm going to click OK. And now we can look in our
Queries and Connection pane, and sure enough, 3.1
million rows loaded. If I hover my cursor, I can
read Loaded to the Data Model. It even tells me the source. Now, this is the
Power Query pane. We can see up in
Queries and Connections, that button is highlighted,
which means that pane is open. Now, before we go look
at the data model, I'm going to click
Control-S to save. And if I go look at the file in
Windows Explorer, look at that. Seven megabytes. There's 3 million rows in that
Excel file, in Power Pivot, in the columnar database. Now sometimes, when
you're learning this for the first
time, it's confusing. I thought this was Excel! What are you talking
about, a columnar database? Well, if we go over to the sheet
Where is Columnar Database, this is Excel. Of course, Excel
has many tools-- formulas, Excel tables, standard
pivot tables, Power Query, and much more. Power Pivot is just one feature. Inside of Power Pivot,
there is a data model. And from that data model, we
make data model pivot tables. But of course, as we talked
about for the first time last video, the data
model is made up of the columnar
database, relationships, and DAX formulas. So we can look at a preview
of the columnar database, but we actually can't see
exactly how it's stored. Now, Data Ribbon tab, Manage
Data Model, or, of course, Power Pivot, Manage Data Model. So I'm going to
click, and over here in the Power Pivot for Excel
preview of the data model, we can see our table. And sure enough,
down there, it says that there's 3.1 million rows. Now, this is not the
columnar database. This is just a preview. I'm going to close this window. Now, as I said
just a moment ago, we actually can't go look
at exactly how the columnar database stores data. As a small example to help
us with the concept of how we take a table and compress
it to such a small size, here's a single table. We have three columns. Now, when we import this
table into Power Pivot data model, the actual
columnar database, it takes the individual
columns, and it only stores a unique list of
each one of the columns. It also builds a
sort of map that allows it to take the
data that's stored, that has a really small
size, and rebuild it when it needs to make a calculation. Now, the compression process
to create the columnar database is called Vertipaq compression. And it does all sorts
of amazing things, including proprietary
methods and techniques that only Microsoft knows. So we know that
it takes big data and it compresses it small. But just as a little glance,
let's look over here. If this is our column, one
of the methods it will use is, instead of storing
all this text data, it will actually store a
unique list with an integer value as the first column. And then, instead
of keeping these, it will store the integer
values in a column. This process right here is
called dictionary encoding. And it can take a single column
that takes up lots of space and reduce it down to
a much smaller size. Also, very importantly,
the internal engine, when making DAX calculations,
has a much easier time working on integer values
than it would on text values. Also, another
glimpse of what might happen when it's compressing the
data, here's that same column. But if there's lots
of repeat values, and it's sorted
in a certain way, it might take the
dictionary encoding, and if it detects lots
of repeated values, instead of storing the integer
column in the dictionary, it will do this step first. But then it will store
the dictionary, and just a list of the IDs for
each one of the items, and then how many times
each value is repeated. Now, that's probably
more information than we need to know,
especially at the beginning of this process. But columnar database
inside the data model, totally amazing, because
it takes a lot of data and compresses it down. Now, I want to go
over to the sheet Pivot Table, because we do
want to make a pivot table. Insert Pivot Table, or simply
use the keyboard, Alt-N-V. Because we have something in the
data model-- in fact, for us, a single table-- it assumes we want to use the
data model on the existing sheet. Click OK. Here's our table. I'm going to click to expand. Product down to Rows. And just like that,
hey, wait a second. It got a unique list,
probably because underneath, it had that unique
list already stored. Now I'm going to take SalesRep
and drag it above Products. There's each one of the
sales reps, and the products. Now, we talked about, for
the first time last video, an implicit measure. Well, remember this
is an Excel trick. All we did is take 7
million rows of text data, store it in the
columnar database. Now we're going to make
a simple pivot table, not actually authoring
our own DAX formulas. So when I drag Sales
down to the values, it will quickly calculate
the totals for each. That's called an
implicit measure. And next video, we'll get
to compare and contrast implicit measures to explicit
measures, which is just us creating the DAX formulas. Again, it's perfectly all
right for our job here-- import a lot of data,
make a pivot table-- to use implicit measures. Now I want to show you
one last important thing, and it involves
creating a new workbook. So I'm going to use the keyboard
Control-N. That opens up a new workbook. I'm going to use the F12 key to
open up the Save As dialog box. I'm going to call this file
014 import 1 text file. I'm going to save it. And now, what I
want to do is I want to import one text file
that's 38 megabytes, because Data, over to Get Data. For us earlier, we
had lots of files. And that From Folder is amazing. But remember, in Excel,
we get lots of text data. So from now on, we simply
use From File, Text. And I do not want to dump this
file into the actual worksheet 2020, because if I do, sure,
this particular file will fit. But why not put it
into the data model and have a dramatically
smaller file size? I'm going to come down
here and click Import. We need to make sure
the delimiter is Tab. Edit. I'm going to do my same
two transformations. Text, Replace, Date, Add New,
Delete UnitsSold to Price, right-click Remove Columns. Close and Load, Close and Load
To, Only Create a Connection, Add This to the Data Model. Click OK. Now, there's our 720,000 rows. We easily could have put
this into an Excel sheet. But from now on, don't do it. Put it in the data model. Control-S. We're not even going
to make a pivot table. We just want to go
look at file size. And there it is. It's, like, 1.5 megabytes. That is the power of Power
Query and Power Pivot data model columnar database. Now, over here in
the PDF notes, we can now define a
columnar database. It is a behind-the-scenes, in
RAM memory, efficient, big data analytics database. Now, if we break this down,
the behind-the-scenes simply means, when you
open the Excel file, the columnar database is
opened behind the scenes, and is stored in RAM memory. Efficient big data, that
simply means the database encodes and compresses
data and stores it in a structure
that allows our DAX formulas to calculate quickly,
and produces a small file size. Analytics database, that
simply means the database is specifically designed to work
with the DAX formula language to make calculations
quickly on big data. There are also many synonyms
for columnar database. You might hear columnar
database, Vertipaq engine, SSAS tabular, storage engine,
or XVelocity analytics engines. They all mean, we're
storing a lot of data in a compressed
form, and we're going to try and make calculations
quickly on that database. Also, you might be wondering,
why the name Power Pivot? The name came because
Microsoft wanted to use the same amazing
pivot table user interface to drag
and drop fields, to make reports,
but with more power. Now, the "Power"
part of the name means we can make pivot
tables from big data. We can make pivot tables
from multiple tables. And we can use DAX
formulas, which can process over big
data efficiently, and which allow us-- uh-oh, I spelled that wrong-- which allow us more
varied calculations than standard pivot tables. The "Pivot" part, of course,
comes from "pivot table." All right. In this video, we saw how to
use Power Query, Get Data, From File, Text File, or a
folder filled with text files. Import it into the Power
Pivot columnar database, so that we could import millions
of rows of data into Excel, or simply have a
small file size. Now, if you liked that video,
be sure to click that thumbs-up, leave a comment, and subscribe,
because there's always lots more videos
to come from Excel Is Fun, including our next
Power Pivot intro number three, where we're going to have
a comprehensive introduction to all the features
in Power Pivot. All right. We'll see you next video.
