[MUSIC PLAYING]
Welcome to MSPTDA video number 20.
Yes, Microsoft Power Tools for Data Analysis.
And we get to talk about how to use Power Query in both Power
BI Desktop and Excel to create a parameter for a variable
from folder data source.
Now when we're using from folder,
that means we have a bunch of different files.
In our case, we're going to have some different files,
text files that we want to import into Excel
and append into a single table.
Over in Power Query and Excel, we'll
use get data from file from folder.
This is what we'll do.
But then we want to import from an Excel table
the variable folder path as a parameter.
So that later, we can simply type the new folder path
into Excel and everything will update.
In Power BI Desktop, we'll have the same files.
We'll use get data from folder.
Here's the folder address.
But we'll define a parameter first.
And then use that parameter in the folder dialog box.
The end result in Excel will look like this.
Whatever the output, whether it's a table or a report,
I want to simply change the folder path.
And when I hit Enter and Refresh All, the queries over here
update based on this folder path.
And inside this folder, there was a different set of files.
In Power BI Desktop, we have our data or our reports,
whatever it may be.
When we want to change the folder path,
we simply go up to external data dropdown for edit queries.
And look at that, edit parameters.
There are two parameters.
This is the one I want.
So I paste a new folder path.
And when I click OK, I get the new data
from that new folder path.
Now the way I like to use this trick
is I have a particular folder with files
on multiple computers.
So wherever I bring this Excel file to do my analysis,
I just paste the correct folder, the table,
and any resulting reports will update.
On my computer, here is one of the folder paths
that has January text.
On a different folder path, I have these three files.
Step one, we need to bring this Excel table into Power Query.
Data, get and transform from table range.
In the Power Query editor, I see my table.
I do not want to keep this name.
That's the name of the Excel table in the Excel spreadsheet.
I'm going to edit this and call this folder parameter
and Enter.
Now this is a table.
And we need to get at that address as text.
So select the cell and right click, drill down.
Now there's a big problem with this,
because this is a query that's going to deliver
the correct folder path.
But as soon as I put that query into another query,
because this query folder parameter contains
an external data source, we will get the Formula.Firewall error.
But no problem.
Guess what?
This query has the folder path.
We will add another step here and land whatever files are
in that folder in this query.
Then when we use the folder parameter
in a subsequent query, because it's landed to tables
and not this data source path, the query will work.
To add a step, I'm going to click the f of x button.
We can see a custom over here.
That's the name of the previous step, which is text.
And we love Power Query and the way they name functions.
What do we want to do?
Well, we want to go to that folder dot and get the files.
So we simply wrap that function around that text pathway.
And when I hit Enter, we have landed, all of the files
and whatever attributes there are.
Now we can close and load close and load to as a connection,
and we're done.
But I got to show you a couple of extra tricks.
Now folder.files, remember that.
When we drill down, we did a two way look up.
Remember back to video number nine
in this class, that's the table.
There's the row index position.
Power Query is base zero.
So zero means, please get the first one.
And that's the column indicator to do the two way lookup.
Change type, we actually didn't need that step at all.
And of course, source also did a two way look up.
Excel.CurrentWorkbook delivers a table
with all of the workbooks in this particular Excel file.
Curly brackets gives us the row index position
doing key match look up.
And then there's the column indicator.
Right click, change type, delete to end, delete.
OK.
Well, that whole construction right there is delivering what?
A table.
If I want the first row, I simply
do position index operator, curly brackets, zero.
That's the first one.
End curly brackets.
When I hit this, it delivers the row, which is a record.
So I need to add at the end the name of the column
to do a two way lookup.
Square bracket, name of the column, I
better spell it right, close square bracket.
And when I hit Enter, now we did two way lookup
on that first table.
Now I simply wrap folder dot give me all the files.
And there, I have done it all in one step,
landed all of the files from that folder based
on the Excel table folder path.
Now we can go up to close and load close and load to.
Only create a connection.
Click OK.
Now to reference this query and create our final result,
we can either go up to get data and find blank query,
or I can double click the existing query.
Over here on the left, we open up queries,
right click, reference.
Now when we reference, anything we do in the first query
will be reflected in the second query.
And all it does is it says, hey, equals
whatever's in that first query.
Now we're going to name this something like all text files
and Enter.
Now we can do our transformations.
We're only going to have text files.
We don't need any of these attribute columns.
So right click content, remove other columns.
The double expand button, when I click this,
will combine all the text files.
In the first step, we want to make
sure we have the correct delimiter, which we do.
Tab is the delimiter for our text files.
Click OK.
And when I click OK, it's doing a lot of steps.
It created a bunch of queries and used
them to create our final appended proper data set.
Now what did it do?
It actually took the sample file.
And in sample file, if you look at source, oh, look at that.
It took the same source, and we can see it right there.
Then it built a parameter, which it used in a query
where all it did was promote headers.
Then it built a function that it can use over and over.
So next time when we have lots of text files,
that function can be used on each one
to convert it to a proper data set.
Then down here in all text files,
it invoked the custom function, did a few other steps,
and we have our table.
Now I'm going to close and load, close and load to, as a table,
on existing, something like E1.
Click OK.
Now we could have done anything we wanted.
We could have had other transformations.
We could build reports.
But let's test this out.
This folder right here, I'm going
to highlight Control C Escape.
I'm going to paste a new folder path up here.
Enter.
And now all I have to do inside my Excel sheet
with my output or reports is in data, click Refresh All,
or I can use Control Alt F5.
And just like that, now it's pointing to a different folder.
Now we have these different folder paths
on this one computer.
But I like to use this, because I go from computer to computer.
And the folders are on each computer.
And I simply change with an Excel parameter
the folder path and everything works.
Now let's jump over to Power BI desktop.
Now I open up this Start file.
We don't have anything in here.
We want to go to Edit Queries, Edit Queries.
This will open up the Power Query editor.
Right here in Home Parameters, dropdown for Manage Parameters,
New Parameter.
And I'm going to call this something
like, Folder Path Address.
Save it as text.
Here's the current value Control V, click OK.
There is our parameter.
And the cool thing about the parameter
is that when we New Source, down to More, Select Folder,
click Connect, this dialog box has a dropdown.
And now I can say Parameter, and there it is.
Right now, it's the only parameter
in this particular file.
Click OK.
We're going to click Edit.
We're going to name our query, folder path, and Enter.
Now I'm going to come over here and right click.
Uncheck Enable load.
This is like connection only over in Excel.
Right click, Reference.
We're going to give it a good name, All Text Files.
Right click Remove Other Columns.
Click the double downward pointing arrow.
Tab.
OK.
We have the same set of queries, and there's a bunch of steps.
Now we can close and apply.
The close closes Power Query.
Apply sends it to the data model.
Over here in data or table view, there is our table.
Now when I have a new address, I simply
go up into Power BI Desktop, External Data, Edit Queries,
and there it is, Edit Parameter.
Highlight, Control V, click OK.
Apply changes.
And now in Power BI Desktop, we have a variable folder path.
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, including MSPTDA video 21, where we'll
talk all about data modeling.
All right?
We'll see you next video.