For this post, we’ll be doing work in power query, combine files from multiple folders in our network, desktop, OneDrive, or SharePoint.
In this example, we have three CSV files in a folder and the data looks like this:
We will be using power query in Excel, which works exactly the same as power query in Power BI.
Connecting To A Folder In A Network
We’ll start things off on our tutorial on power query, combine files from multiple folders with an empty Excel file and go to the data tab, click on Get Data, From File, and then From Folder.
This is how you connect to a folder that’s on your C drive or on your network. If you want to connect to a file on SharePoint, use this connector.
Use the browse box to search for your folder. Then click on the Combine & Transform Data button.
Then we’ll be asked what type of CSV file this is. Click OK and if it doesn’t work, then go back and change into the correct CSV file type.
We get this big block of steps in power query. This can be a bit daunting and confusing. These three files are stacked on top of each other, which is not what we want to achieve.
We’ll click on the first file and then click on Transform Sample File. Whatever changes we make to this file, it will also be applied to the other files.
Cleaning Up The Data
Let’s tidy this up first. We want this date to fill down against every record. If Column1 contains the word date, we want to put the date on the other columns, and fill that date down.
We’ll add a conditional column that says if Column1 equals the word date, then we want the value from Column2. Otherwise, leave it blank.
Select the date and nulls column, then right click and fill down.
The result will be a column with all the dates.
To get rid of the blanks and the nulls here, click on Remove Empty.
We don’t want to use the first row as our headings, but we also can’t use the Use first row as headers button because it will push the dates into the heading row.
Doing so will change the date for every file. The simplest solution is for us to rename these columns manually.
Select all columns, press right click, then click on remove other columns.
After we edit the subheadings, we are now pretty much set.
When we click on the DemoFolder, we get an error message. We get this most of the time when we make a little change on the transform sample file.
Most of the time, the error is caused by this Changed Type step because it is looking to change that original Column1, which doesn’t exist anymore.
Simply deleting this step fixes up the error.
Everything we did to that first file will now be applied to every file. It really does rely on our source files being structured the same way. It’s not impossible if they’re different, but it is going to be a lot harder.
So let’s look back at these queries and work out what’s going on. If we go to the source step of the sample file, it is referring to this thing called Parameter1.
And if we look across to the left, Parameter1 is called the sample file.
And if we go to the source of the sample file, we navigate to the record 0.
Power query is a zero-based language, which means Record1 is actually Item 0. It’s just grabbing the first file and then the other steps get converted automatically, which is just brilliant. You used to have to manually do this back in the day into the Transform File tab.
If we expand this, it will give a file name and then a run through of all the steps.
If you are to use the advanced editor, you will get this warning. We recommend you avoid doing this because you can’t reestablish them afterwards. Generally, just do all your tweaks in the sample file.
So what happened in this consolidation step is that it went to the folder and it filtered out hidden files or system files. Then it added the invoked custom function, renamed a few columns, and then removed everything else.
The one thing you’ll notice is that it loses the column types. So whatever column types you pick in the transform sample file won’t be inherited by the consolidation file.
We’ll then click on Close & load. Every time we click refresh it will scan through that folder.
Connecting To A Folder In SharePoint or OneDrive
If your files are in a OneDrive or a SharePoint folder, the process is virtually the same, but subtly different. You need the right path for OneDrive, which is the URL up until your name. We’ll click on Get Data, then SharePoint folder.
It will prompt us for a URL where we can paste the link. Then it will list down pretty much every file you have in OneDrive. Once we go into Transform Data, we’ll have to do a couple of different steps. First, we need to find the right folder, which is the DemoOneDriveFolder.
We’ll click on Text Filter, then Contains, then type in DemoOneDrive. If we don’t do this, we could be scrolling for ages and not find what we’re looking for.
This process will then filter it down to the right folder. As you can see, we have the three files. Excellent.
We’ll go to the Folder Path column, right click, go to Text Filter, and choose Equals.
This way, we can grab the proper path rather than the contains part. We can just get rid of the latter step because we don’t need it.
Another bit of advice would be to create a filter where the text filter equals CSV files – just in case some other files get dropped in here.
The last recommendation is to call this query OneDriveFolder and then reference it. It’s useful when you’re trying to debug later on and need to work your way back, or even when you have to change the folder.
This would be more complex than the earlier example we worked on. For this one, all we need is the Content and Name columns.
We’ll just right-click to remove the other columns.
Click the little double-headed arrow, which will combine the files.
From this point onwards, the process will be identical to the previous example, where the helper queries were also created.
Just a quick note that if you are consolidating Excel files, one of the intermediate screens that pop up will ask what sheet you want to consolidate. The sheets really need to have the same name because if you’re consolidating multiple Excel files, it can get messy.
Here’s our transformed sample file all neatly tied it up exactly as we did in the previous example.
And finally, here’s the consolidation file which will be pushed out and loaded into the data model.
And we also have this one extra folder named OneDriveFolder. Lastly, don’t forget to rename your steps with something useful and meaningful.
***** Related Links *****
Organizing Your Queries: Power BI Query Editor Tutorial
Power Query Best Practices For Your Data Model
Organizing Your Queries Into Groups In Power BI
Conclusion
For this tutorial, we worked on power query, combine files from multiple folders from our network, desktop, OneDrive, or SharePoint.
If you enjoyed the content covered in this particular tutorial, please subscribe to the Enterprise DNA TV channel. We have a huge amount of content coming out all the time from myself and a range of content creators, all dedicated to improving the way that you use Power BI and the Power Platform.
Sam
