In this video, we want to see how
to use Power Query, the most amazing invention in Excel
since the pivot table, to take two text files
and import the files directly into a pivot table
to create this report.
Then, when we add extra text files later,
all we do is click Refresh and everything updates.
Now, when you download the files,
you'll have to unzip this folder here.
Inside, there's a StartFiles folder and an extra file
we'll add later.
If I double click and open this, right click, Open With,
and I'm going to open this text file with Word.
So we could see, very importantly, the delimiter--
that means what separates the columns
in this proper data set--
is the tab.
Now, these files were exported from a database,
so each file will be exactly the same.
That's what we need when we want to easily append them
into a single table for our pivot table.
Over here in the file that we used in the last two videos
to create a formula solution and a standard pivot table
solution, I'm going to come down and click the plus,
and rename this something like PQ Report.
Up in the data ribbon tab, these two groups, Get
and Transform, Queries and Connections--
that's our Power Query.
To import, we go to Get Data, From File, and over
to this amazing invention, From Folder.
We click Browse.
And we have to navigate all the way down.
And what we're doing when we click on StartFiles
is we're pointing Power Query to a folder.
Power Query will try to import everything from that folder.
Now, we only have text files here, so it will be easy.
Click OK.
Power Query is going to memorize that folder path.
If you change the location of the file or the folder path,
you have to come back later and edit this,
and I'll show you how to do that.
Click OK.
Now, if we had a complex transformation, we'd edit it.
But because we have text files from a folder,
and they're all the same structure,
we can click Combine and then Combine and Load To.
This will combine the tables and jump us directly
to the dialog box for importing the data into the pivot table.
Now, it'll first ask us what the delimiter is.
In the Combines File dialog box, make
sure the delimiter is a tab.
Then click OK.
Now, it did a lot of steps behind the scene,
but when you have simple text files all in a single location,
that's how easy it is.
Now, we do not want a select Table,
because that would put it into the sheet.
That might be what you want, but not here.
We want to load it directly to the pivot table
report or the behind the scenes pivot table cache.
If you loaded it to the table and then made a pivot table,
it would actually store it in two different places.
I'm going to select Existing worksheet,
because that's where our pivot table is going to go.
Click OK.
Now we can see that's where our pivot table will be.
Here's the pivot table field list.
And over here is Queries and Connections.
These are all the queries that were created when we combined
or appended the tables into a pivot table cache.
Now, down here, that's the actual table.
These are a bunch of steps that it did automatically.
Now, we want to go look at this table,
because we can go back into the query and change things.
Or, for example, I want to change the name of this.
So we can double click.
This opens up the Power Query Editor.
There is the name of our query.
Let's rename it, something like All Text Files Proper Data Set.
Now, these are the steps that it did to create this table.
Over here, we can see the different columns.
If we scroll over, there's all the columns.
Now, one thing we do want to do besides changing
the name is we don't need that column right there.
So watch what happens.
The last step is Change Type, right click, Remove.
Now it added an extra step.
If any of these data types didn't come in
with the correct data type, and you can tell what
data type it is by the icon, you can also
click to see what the different icons mean.
But if it didn't get the right data type,
you can change those.
For example, you could simply click the icon
and select whichever one you want,
and it would add a new step.
Now, we will come back into this window later
and change a few things, but for now, I'm
going to click Close and Load.
And we can see right away that that extra column
we didn't need is gone.
Now, the goal of the report is to count the hours that
are less than 0 for Name, Description, by the Month,
and by the Disposition.
The first thing we want to do is group
the dates by month and year.
So we grab transaction date, and I'm
going to drag it down to rows.
Immediately, it groups it into years, quarters, and month.
I'm going to drag quarters off, years over to column.
We can see over here we have months and years.
We want months over in the columns,
too, so we drag this over.
Over in the pivot table, I'm going to click in B1
and rename this, Months.
And Enter.
Now in the year, where we see the plus,
we can expand by right clicking to Expand and down
to Expand Entire Field.
The next thing is we want Name down in rows.
That'll give us a unique list of names.
We can drag Description down to columns.
Now we have a unique list for each month.
Now, we could drag Disposition down to Filter.
That will give us a condition or criteria for counting
for the whole report.
But a much better way is Pivot Table Analysis tab,
over to the Filter group, and we'll select Insert Slicer.
We can check Disposition.
Click OK.
We can resize it and move it.
Now, when we click back in our pivot table,
we now have all the conditions and criteria
for our crosstab report.
But there is a problem.
Work hours has negative and positive numbers.
So if we drag this down to values, it's a number,
so it defaults to Sum.
But I can right click, Summarize Values By and Count.
We have count of work hours, but it's
counting everything, positive and negative.
Well, here's the thing.
If this report is only going to account negative numbers,
we can actually go back into Power Query, which
is the connection between those text files and our pivot table
report.
I'm going to double click.
Anything we do here in Power Query
is reflected in the data inside the pivot table cache.
So I can simply come up to work hours, Filter drop down, down
to Number Filters, Less Than, and I'm going to type a 0.
Click OK.
This column now only has negative numbers.
When I click Close and Load, now the data
loaded into the pivot table cache
has only negative numbers.
Now, the last thing we need to do is get rid of subtotals.
Design, Layout, Subtotals, Do Not Show.
And there is our report.
If I click Transfer, Time In, and Divide.
Now I want to save this.
Back in Windows Explorer, we want to take our February 2002
file, Control C, double click Start,
Control V. Because Power Query is
pointing to that folder, when we click Refresh
in our pivot table, Power Query will redo all the steps
on these three text files and will
have the single correct table in the pivot table cache.
So back over here, we come to data.
Click Refresh.
And there we have our updated report.
That is pretty amazing.
Now, very importantly, since this pivot table
is pointing to source data in a folder on this computer,
if that folder changes or we send the report to someone else
and they have a folder on their desktop,
you have to edit the source.
You come back to the query, double click.
You can already see we have an error.
On the left, open up Queries.
And in two different places, you have to edit the source.
The sample file, over here, there's Source.
Click the gear icon.
We use the Browse button, and we navigate to the new location.
There's the StartFiles folder.
Click OK.
And then we have to do it to our final table.
Step number one in Applied Steps, Browse, New Location.
Click OK.
And then close and load.
All right.
That was a lot of fun-- taking a bunch of text files,
appending them into a single table,
dumping them into the pivot table cache,
and making our report.
All right.
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.
And if you want to check out some other ways
we did this very same report, a standard pivot table
or formulas, check out these videos.