Power Query Combine Files from Multiple Folders

Power Query: Combine Files from Multiple Folders

No comments

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:

power query combine files from multiple folders

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.

power query combine files from multiple folders

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.

power query combine files from multiple folders

Use the browse box to search for your folder. Then click on the Combine & Transform Data button.

power query combine files from multiple folders

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.

power query combine files from multiple folders

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.

power query combine files from multiple folders

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.

power query combine files from multiple folders

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.

power query combine files from multiple folders

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.

power query combine files from multiple folders

Select the date and nulls column, then right click and fill down.

power query combine files from multiple folders

The result will be a column with all the dates.

power query combine files from multiple folders

To get rid of the blanks and the nulls here, click on Remove Empty.

power query combine files from multiple folders

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.

power query combine files from multiple folders

Doing so will change the date for every file. The simplest solution is for us to rename these columns manually.

power query combine files from multiple folders

Select all columns, press right click, then click on remove other columns.

power query combine files from multiple folders

After we edit the subheadings, we are now pretty much set.

power query combine files from multiple folders

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.

power query combine files from multiple folders

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.

power query combine files from multiple folders

Simply deleting this step fixes up the error.

power query combine files from multiple folders

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.

power query combine files from multiple folders

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.

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

***** 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

***** Related Course Modules *****
Power Query M Masterclass

Applied Problem Solving with Power Query/M
Power Query Series

***** Related Support Forum Posts *****
Power Query – Importing Data From Folder
Tables Not Loading In Power Query
Collapse Query Groups
For more power query queries to review see here….

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.