Welcome to Highline BI348 Class Video Number 38.
If you want to download this file,
BI348 Chapter 02.5Import08 for the Excel file or for the text
file.
And don't forget, this video is about importing
4 million rows of data.
So you've got to get that zipped folder and unzip it.
Now let's go take a look inside that folder.
Here's the folder unzipped.
There are actually eight text files.
And that will be our first example.
Each one of them is about 28 megabytes.
And then there's this crazy Excel file.
And if I open it, each sheet here-- and there's
eight sheets-- and each sheet has like 400,000 or 500,000
rows of data.
So we're actually going to import this.
And if I close this and go back and look,
that file is 177 megabytes.
So we're going to actually import that one in one example,
get all the sheets together in one data set.
And in the other example we'll import these text files.
But in both cases we'll take a lot of data
with a big file size and reduce it down to a small file size.
And our goal is ultimately to take all the sales data
and make a pivot table.
Now I'm going to go over to our first sheet, eight text files.
And this is in the text files.
Now let's go to Power Query.
And we've seen this a few times before, From File, From Folder.
We tell it where the folder is.
There it is.
And when you use Power Query to look at a folder,
it will import everything.
Now remember, there is some text and some Excel in there.
So I'm going to click OK.
And this opens the Power Query Editor.
I want to make sure and name this.
Give it a smart name, import eight text files
4m rows, for 4 million rows.
Now, this is the text files, so I want to be sure and come
to the Extension column and filter out the .xlsx.
Click OK.
Now because these are all text files,
we don't need any of this.
I can right click.
Remove Other Columns.
Text files are certainly a lot easier to import and gather
up into a single data set than Excel file.
I'm going to click this little dropdown.
And we're allowed to do this and expand
because it's a text file.
Now it did a few steps over here.
If I go back to Import CSV, you can see it got all of the data
sets.
And the first text file's field names are listed here.
But notice in the second to last step here,
it promoted those field names into the first row.
But guess what?
That step only did the first file.
There are seven other files below that
have these field names listed as records,
and we don't want that.
So the trick is, we want to pick whichever
column has the fewest number of unique records.
And I'm going to select Website.
Now when you use this Filter option on a large data set,
it will say List May Be Incomplete.
And you always click Load More.
And now we have a complete list.
And there's Website.
Notice that that is the field name.
If I filter out by unchecking Website,
it means down below it found Website in this Website column,
but it will filter out all of the field names
in those files below.
I'm going to click OK.
And there are our steps.
There is our name.
I'm going to close and Load To.
Now we want to check Only Create a Connection,
and then add this to the data model.
The data model is where the data will be stored,
and the data model is efficient at storing data
in a much smaller file size.
So when I click Load, now it's all loaded,
4 million rows there.
But I want to go look at this sheet, Columnar Database.
Because that's what the data model is, a columnar database.
It takes a data set like this, with field names and records
and rows, and stores each one of the columns
in a separate column, only storing the unique records,
and builds a map that connects the actual raw data stored
in the columnar database back to the original data set.
Now this columnar database is efficient at reducing file size
and at performing analytics on the data set.
Now I want to come back over to eight text files,
and we want to access that data model and those 4
million records using Insert Pivot Table,
or we can use the keyboard Alt, N, V.
And we want to use an external data source.
That means we're actually accessing the data model, which
is not in the sheet here.
But it's external in the sense that it's behind the scenes.
Choose a Connection.
Over to Tables.
Now this data model-- I'm going to select the data model here--
and later next week, we'll see how
to not have a single table but multiple tables
and build relationships.
But here we just have a single table.
And click Open.
Click OK.
And whoa, look at that.
I can uncheck the table.
And all I want to do is see-- I'm
going to bring Website down to rows
to get an instant unique list.
And I want to sum the quantity sole each website.
Immediately come up to Design, Report Layout, Show In Tabular.
Right click Number Formatting.
And I want to see something like number with a comma.
Click OK.
Now Control S, because we want to go look at the file size.
And here's our file, 18 megabytes.
So it took 4 million records and reduced the size down
to 18 megabytes.
That is pretty amazing.
Now we want to do the same thing,
but we want to do it from an Excel sheet.
So I'm going to go over to the workbook,
Chapter 02.5 Import08 Excel files.
So I'm on the eight Excel sheets.
Power Query, From File, From Folder.
Browse.
There is that folder.
It's got text and Excel.
Click OK.
And there it is, all the information
about the files in that folder.
I'm going to be sure and give this a good name.
I gave it the name Import 08 Excel Sheets 4 Million Rows
and Enter.
Now this time we want to filter out the text
and leave only the one single file.
And click OK.
I don't need any of the other columns.
Right click, Remove Other Columns.
Now this little button here to expand only works for text.
For Excel we have to add an extra column.
We've done this a few times already.
I'm going to call this GetExcelData tab.
And the Power Query function Excel.workbook.
Open parentheses.
And I'm going to say, hey, go look
at the content, which could be things like sheets and tables
and defined names.
I'm going to click OK.
We don't need content now.
Right click Remove.
Now I can click the little arrow going to either side to expand.
Remember, that's the data.
That's the sheet names.
That's the [INAUDIBLE] like table defined names and sheets.
Click OK.
Now we only have sheets.
We actually don't need the sheet names.
We don't need anything except for the data,
so I'm going to right click Remove Other Columns.
Now we can click that little button to expand.
Click OK.
It's not quite as polite as the text.
We're going to have to come up to the upper left hand corner.
Click the little icon and say Use First Rows as Headers.
We have the same situation where we
need to filter out all of the field names below,
so I'm going to select this column.
It has only a few unique items.
Click Load More.
There's my unique list.
I'm going to filter out Website to get all those field
names from below.
Click OK.
I have my steps, my name.
Go up to Home, Close, and Load To.
I'm only doing a connection.
And I'm going to add this to the data model.
And click Load.
And there it is, 4 million plus rows of data
from our Excel workbook.
Now we want to access it using our Insert Pivot Table or Alt,
N, V. I'm going to use an External Data Source,
Choose a Connection, over to the Table tabs.
We have one table in our data model.
So I'll select that and click Open.
Click OK.
In the Pivot Table field list I drag Website down rows,
Quantity down to Values.
Oh, look at that.
It's counting!
Now on Number field dropped into values
defaults to some that can only mean one thing.
That source data is incorrect.
I'm going to come over to the Query.
Right click Edit.
And sure enough, look at that.
That's Date and Time.
That one is any.
I want to change the type to Date and Time.
And each one of these, Text, Text.
Quantity should be whole number.
Discount in Retail, I can highlight them
both in decimal number.
And now when I click Close and Load,
it will automatically reload.
Now I'm going to drag this off and try this again.
Let's try Quantity Down to Values.
And just like that, it defaults to the sum.
Right click, Number Formatting.
Number.
Use a Decimal.
And I definitely want zero decimals.
And click OK.
Wow.
That is amazing.
I'm going to Control S and go look at the file size.
So the Excel file is about 24,000
and the text file is about 18,000.
But that is amazing.
We significantly reduced the file size.
But more importantly, we imported 4 million rows of data
from text and from an Excel file and made a pivot table.
Next video we'll see how to import multiple tables
and create a relationship between the tables,
so we don't have to use V Lookup.
All right.
We'll see you next video.