Combine Files from a Folder with Power Query the RIGHT WAY!

Most people combine files from a folder with Power Query all wrong. In this video I show you why it’s wrong and the way you should be doing it.

When Power Query combines files in a folder it automatically creates a bunch of queries for you. There are two queries that are important, the sample file query and the final query.

Most people use the wrong query to make their transformations and wonder why they’re struggling.

Check out the video to see examples of when and why you should use the sample file query vs the final query.

🔔 SUBSCRIBE if you’d like more tips and tutorials like this.

📢 Please leave me a COMMENT. I read them all!

🎁 SHARE this video and spread the Excel love.

Or if you’re short of time, please click the 👍

⏲ TIMESTAMPS
0:00 How to Combine Multiple Excel Files From a Folder
0:20 The Data & Where to Save it
1:08 Getting the Data From a Folder
3:01 The Important Queries
3:57 When to use the Sample Query
7:36 The Important Stuff
7:53 Loading the Data
8:51 Getting New Data

[StephenZipprich]

Rather than manually refreshing, go to Data Ribbon > Queries > Properties and set it to refresh whenever the file opens. This ensures you dont have to remember to refresh the data, and potentially have an error in your output.

[thatJustinUknow]

Love how you don't mince words, and keep it clear by comparing and contrasting, and telling WHY to do a thing as opposed just explaining WHAT to do, thanks!!

[mobe]

Great video, I always overlooked the sample file. Now I know it's worth. I also loved your example with transposing the column headers - I am sure I will be using that sometime in the future!

[dmytrofryashchikov]

Hi Mynda,
Your channel is the best Excel-related resource I've ever seen in my life.
Keep up!

[mcegirl]

I'm still trying to get a handle on PQ, and you are helping immensely! I had a couple of 'light bulb' moments watching this video - thank you!!

[emmanuelle]

Excellent ! Very clear explanations as always.
Just used them and 💥... my work has been produced in half the time I'd previously planned.
Thanks you very much !

[williamarthur]

Loved the way of promoting two rows as a header I've always ended up isolating the first two rows,
Table to columns, Table column names, zipping and renaming. But thanks for the whole thing,
I still find brining in files form a folder with different names and structure very confusing and there's a lot of trial and error.

[nazarkamal]

Before combining do it in sample / After combining do it in either sample query or in final query !!! Well said this is what i want !! Searched in so many channels regarding this difference finally got from yours channel 👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏👏 thanks 👍

[notesfromleisa-land]

Great video. I would add that you can combine pdfs as well from folder. I did this for point of sale reports in order to automate a journal entry go uploading. Dropped all weekly sales for each store into a discrete weekly folder. Grabbed the data from the folder. Repoint each week to current weekly folder. Power query has allowed me to save my clients time and money while improving accuracy.

[KathleenChartier]

Another great video on how to make use of the great functionality in Excel!

[josephdelvecchio]

Thank you. Yours is the only video I was able to find that explained the need to apply transformations to the sample file to cause them to be applied to all files in the specified folder before combining the data. My issue is that my weekly CSV source data is formatted is such a way that there are data in multiple discontiguous tables arranged vertically throughout the file which need to be collected and then combined into one table. The only way I've been able to accomplish this is creating multiple duplicates of the original sample file, each of which take different data from different areas of the source file. I use the original sample file to get the first set of necessary data, the first duplicate to get the next set, and so forth. The resulting data sets from each query do not have matching columns but do need to be put together to create a complete table. So I insert an index column in each query's data set and then merge them using the original sample file. However, the final query table only shows the first set of data from the original sample file for each respective source file. All other data for each of the duplicate sample files is just repeated down the column for every respective source file. I imagine this has something to do with the fact that only the original sample file is respected in this manner, but I don't know if/how I can fix this or if I'm taking the wrong approach to this altogether. Any help would be appreciated.

[IvanCortinas_ES]

Great video Mynda. As always a clear explanation!

[ersofrescht]

Thanks! This was useful. Your tip on naming the sheets exactly the same helped me resolve the error "[Expression.Error] The key didn't match any rows in the table." I kept getting in loading excel files.

[AchmadHilmanS]

I just wanted to say a huge thank you for all the amazing tutorials you've shared! Your tips and tricks have completely transformed the way I use Excel. Every video is packed with useful information, and your clear, step-by-step instructions make everything so easy to understand. I've learned so much from you and now feel much more confident with my Excel skills. Keep up the fantastic work!

[ivanbork]

Hi Mynda
You truly are a genius, I get a lot out of watching your uploads.
Use PQ almost every day, and never stumble over this, but instead use filtering out and renaming columns.

So grateful, thanks

[yuhooh]

Great for share me the tips, edit Transform sample files query if we want to clean and transform before combine.

[reginaldarbruthnot]

absolutely outstanding video - superbly presented - detailed yet simple and clear and easy to follow - outstanding! Thank you so much.

[saumilparadkar]

I use PQ at my workplace and end up doing many things manually... This video was extremely insightful... Thanks Mynda

[Lyle-In-NO]

Absolutely awesome! Thank you for sharing this.

[azmatmalikLTU]

The best thing i notice besides the video is that you responded each and every comment. ❤