Combining Multiple Files from a folder using Power Query in Excel or Power BI ( ⚠️see description ) 

Wow this is great! I've been curious to know how the other buttons work in that interface but I simply cannot afford the time to bump into obstacles and spending the rest of the day working on fixing those obstacles. I didn't even know such trick existed 3 years ago! Thanks a lot for sharing your knowledge, Wyn! This is so good!

I like this guy. easy to understand. No nonsense. Very explanatory.

Best explanation of files in folders I’ve found on YT. THANK YOU!

What a brilliant lesson - Thank you very much. (I'm from a sound engineering background - When listening on headphones there is a lot of low frequency interference - bangs bumps etc. - I would suggest that by putting a low frequency filter on the audio, removing everything below 100 Hz.

I can’t believe he doesn’t have more subs. He was the only one who answered my struggle with SharePoint. Not this video but the SharePoint folder one. Mind blowing!

Helped me a lot to understand the principles of Power Query. Very well explained and easy to follow. Brilliant! Thank you so much!

You will never know how much you helped me with this video. Many thanks and God bless you real good.

I spent several hours trying to figure out on my own how I’d broken my attempt at this today 😂 thanks for the help! Solved my problem

Tks so much for the detailed video! I just combined 10 files with Power Query !

You can do it in many ways, but this was really lean och quick! The best way! Many thanks!!

I finally understood what was going on in there. Thanks Wyn.

My go-to guy! Precisely what I needed. Thank you, Wyn!

Thanks for the perfect and paused explanation.

Woooow. It is comprehensive explanation. i was trying two days in youtube searching an explanation like this. Thank you Access Analytic. Specially meaning of "Content (0) ", details abt transform folder.

Wow! Your walkthrough of the helper queries was more profound that I thought it would be. Opened my eyes to even more Power Query potential. I've been fooling around with PQ for a while, but now you make me want to go steady. 🤣

Great video, I've done this many times and never really thought about changing the Transform Sample File. Good explanation of what the steps are doing.

What a GREAT explanation! Very clear, conscience and in plain terms - I can now properly move forward with my Power BI Power Query, thanks 🙂

Excellent! used it before, but never realized how it can be improve the overall process

Oh wow, this was great. I've watched a few videos on this but none of them explained it as well as you did. Thanks a lot for explaining the "why" and not just the "how"!

Thank you so much! I was struggling on combine and transform some pdf's and this video made my life so easy haha!

It is excellent. I clearly understand why so many queries were created while importing from a folder. Now, I can re-use the helper queries for multiple instances of imports from the folders. Thank you for your effort...

Thank you for this very helpful tutorial. I'm new to PQ, and was able to start using it effectively after this 20 minute tutorial NICE!!

Thanks Wyn. Clear description of the intermediate steps. I like your tip to have a reference query for the folder. That saves so much effort when the path changes - which happens all too often.

This was such a big help!! I stepped into a project and things are very different from how I usually work. This explanation helped me immensely!! I appreciate your help more than I can say! Thank you!!

Great vid Wyn! Thanks for explaining step-by-step n the differences between the two approaches

This is fantastic! Thank you. This also helped me learn about parameters

Oh you help me a lot .Before this I had to build customs function for transformation.

Thanks Wyn, you have saved me again 👍

Specific, to the point, informative, simply brilliant Thanks so much for sharing your knowledge, and much more thanks for sharing them in a smart time saving videos :)

Very clearly explained. Thank you sir!

Super helpful. I was wondering what those autogenerated steps were doing and how they were created in an existing query I was looking at.

Thank You So Much, This is really very useful.

Brilliant explanation. Thank you Wyn!!!

Thanks so much for explaining this transformation. You really simplified it for me. :)

That was really good, I've always used add column excel workbook, but even with a an excel file I just expanded in the binary and was able to make changes using the generated function,

Thanks for doing this video it is really helpful .

Thank you! You really saved me at work today!

Awesome, thank you very much, you saved me!

sir,useful video for student of power query

Very useful and simple explanation

Thank you, pretty informative!

Exactly what I was looking for, thanks!

This is a great video in the series on this process. Question - in the file name there is data that is needed in the record - how would I pull the file name into a new column before the combine where I lose that?

Wow, this was really great and easy to understand. You mentioned that it would be a lot harder if the source files were different but not impossible. Do you have a video on that or can you point me to a resource on what to do in that scenario? Thank you

Really helpful video, thanks Wyn!! I particularly found the explanation of the helper files useful, and I didn't know you can use the Transform Sample file to tidy up the files before they get combined. I'd previously been doing some awkward thing to filter out all the column headings after they had been appended...🙄 BTW, I'm really looking forward to series 2 of your podcast too :D

Excellent vid. Goes to prove you can always learn something even if you've been doing a process for a long time. Good on ya Wyn

Another great video. Thank you!

Very nice video! thank for explaining all those intermediate steps!

Very freakin' useful, thank you!! I tried load from folder multiple times and not understand all the queries created was an issue, so I'd delete and load individually. I'm going to give it a whirl!

Hi, thank you very much for this video. This is really helpful for me! I just wanted to check about the workaround you mentioned in renaming multiple columns instead of making the first row as headers. In which video have you covered it please ?

you are awesome! Thank you!!!

Thanks for the clear explaination. I only have one question. Can you help me understand how a change (ex. Input of the smiley) in the "Transform Sample File" flows though in the "Transform File" Custom function? When I change something in the 'Transform Sample File", it does not change anything in the "Transform File" custom function. Thanks in advance, Robin

Wonderful explanation, thank you! May I ask how to remove the last column of all the files in the folder? It is worth noting that last column, in my case, is not always the same column in every file (ex. some time it's column R, other times S and so on). Thanks in advance

Thanks for sharing Wyn! Very clear and detailed 👍

Great video. Although I had an issue with the "The key didn't match any rows in the table" so to resolve it I had to format all of my xlsx files as tables then it seemed to work ok.

Wow, great video! Thank You. I have a case where I want to connect to a hidden tab in an excel file stored in network, is it possible to do that? When I click combine on my file, it does not show up the hidden tabs.

Hi Thank you for the excellent sharing! A bit question, how would you the power query if the Share Point Folder are owned by other people (only shared to us)? Many thanks in advance

Super helpful! If there’s only 1 worksheet that contains a table, will this process ‘find it’ or do I have to specify the worksheet by name?

Very clear vid, but I don't have the "sharepoint folder" option in the "Get data>From File" list - I believe it's not an option with 365 business. How do I get information from multiple files in a folder from my org's sharepoint?

Great. Thank you!

Thank you, this is so useful! Just wondering if it's possible at all to use this process to combine multiple excel Sharepoint files with some files that have a few additional columns. The main columns that I need to combine are all named the same in each file, but some have extra calculation columns that are unnecessary for my consolidation. Any insight on how to transform this would be really appreciated!

İt' s very useful video 👍👏

thanks for this video! my issue is clicking on refresh all takes time to update content by triggering the external connections. So, i need to automate this. is it possible to refresh via power automate without manual trigger?

Great video, you are referring to a video if we have a lot of columns for headers, I didn't find it. How should I do if I have a lot of columns, to not rename every one of them

what office version do you use sir? Can I do what you did in ms office 2013? By the way, thanks for the tuts. Very helpful.

Great video. When did the ability to get files from SharePoint folder appear in PQ? Great to see its on the options now and not have to use workarounds. One draw back appears to be the need to have access to the whole site. Ive just built a query that allows consolidation of project files but if those files are in a site that has multiple libraries that have restricted access and the PMO team only have access to one library on that site I presume the query wont work for them? So this powerful functionality has implications on the site architecture eg in this case I will need to create a PMO site which the PMO team can have full access to?

Hi This is one of the most helpful videos I've found so far, thank you! I was getting so confused why so many queries were created on the left hand side when importing from a folder on Power BI and this explains it all so clearly. One question I do have - do you know whether there is a way to combine only a select number of rows from each file that you're importing? I have a folder with a new spreadsheet of information for every day, for example, and I'd like to combine them but each day's spreadsheet has 25,000+ rows. I only actually need to import and combine the first 5,000 from every file and combine (as they are sorted to show the most important products in the top 5,000 rows and these are what we're interested in analysing). I've tried adding a 'FilterRows' parameter but with no success so I think I may be doing it wrong. If you have any answers that would be amazing. Thank you, Rachel

Garcia got that CTE new Power

Thank you soo much for the super useful content. Can you please help me on the error when connecting multiple excel files on share points to Pbi. Some reason it won’t recognize my .Xlsx files keep giving pop up message it can’t recognize data format and use csv, txt, excel etc yet all of my data set is all excel in xlsx. Thank you soo much in advance.

Hello, It was very informative video but the challenge I am facing with the share drive is, if that share drive folder is not mine but someone else shared that folder with me? Any help would be appreciated. Thank You!

Great info, do you have a recommendation when within a Share Point Location we want to append certain (not all) the files in that folder?

Hi Wyn, you refer in your video to DO NOT 'use the first row ...' and you will show this in another video. Can you please forward this video? thx in advance. and thx for your clear explanation of PQ. it is a great help for me. grt Allard

Another Excellent video! One question though when consolidating excel files from a sharePoint folder how do I get the consolidated file to include the File name of the source file - PQ does this when I consolidate from a local drive vis the Transform and Edit button - I need this as I use this to show trends in the data from version to version

Thanks Wyn, Was wondering which is more efficient, to apply the transformation on the sample file or on the consolidated file. I have noticed no major performance difference in both scenarios while combining 50+ files in Power BI query. What do you suggest. Thank you.

Great content! From Sharepoint/Onedrive will also in Excel? Thanks for alle the tips 👍

Very cool video! 😊 I just have one question. If I understand it correctly, in both ways there is your individual name/account in inside the file path like in 6:34. In this case it is not possible for other users to click on the "refresh all data" button because they have access to the files but no access to my personal file path. How can I change that? In our company many people need to have refresh the data from the power query

Excellent ... I have some problem for which your guidance is requested: *I am using excel files (monthly basis) as get data from folder option *All excel files have same pattern (for info only) * Each workbook have 12 sheets (some times there are more than 20 sheets) *Each file have first four rows and at 3rd row date is mention.(sys run date: 31-JAN-2022) in one cell *Below the that date there are transactional columns Suppose have following pattern: 1row:FBL company limited 2row: blank 3row: Sys run date: 31-JAN-2022 4row: blank 5row: haves headers How can I get the date from such situation?

Thanks Wyn! Your videos are incredibly clear and helpful. I'm still having an issue with the "Expression Error: Column 1 of the table wasn't found" problem though - I've been stumped for days about it and I got so excited when you mentioned the fix by deleting "Changed Type", but when I Close & Apply I still get the same error popping up. Any further suggestions for this? For reference - when I linked to these files in a local folder I didn't have this problem. I'm now trying to link to the exact same files in a SharePoint folder and having this issue. The only changes I'm making in Transform Sample File is to delete the first row and promote headers. I can see the column there - name spelling and capitalization is correct - but the error persists. Any guidance would be so appreciated. Many thanks!!

Hi Wyn. Many thanks for this. I've managed to combine the files in a SharePoint folder with your helpful guidance here. Is there any way to add a new column to the Consolidation query that has the source filename for each row of data? I'm doing this in Power Query, and my Consolidation query doesn't have the Source Name column, which yours has retained. Not sure why.

Great video that I was introduced to at the Global Excel Summit 2023. However, I don't have a 'From SharePoint' option displayed - do I need to activate this in some way (I have a full Business 365 licence) and SharePoint folders Sync'd. I used 'From Web' instead, and seemed to give the same options. I selected 'Organisational Account' and signed-in/Authenticated. but when I click 'Connect' I get "We couldn't authenticate with the credentials Provided". I get this continually, and have tried several accounts - same thing - what am I missing on the set-up front?

I’m running into some issues and I think it is because the files I am combining have a varying number of columns. My reports are property comparisons of income statements for a single month so the files I’m combining are other monthly reports. We close and open new locations so that explains the varying column numbers. Would you happen to have any advice on dealing with varying number of columns? Please and thank you in advance! P.S. Your videos are amazing!

Thanks. Great vid. Do you know how i can get the file name of every file into a column? (A side the information that is already in every file?) Sometimes a date or name in the filename can help to determen what the sourcefile of that row of data was.

Thanks so much! Would this work on a Data Lake folder as well?

Thanks for the video.. I am able to extract data from nearly 1000+ folders, in different locations. Had to use sharepoint.files. Then formatted and consolidate it. But my resultant data set is large, but less that excel limit. My problem is I need the final output in an excel. The load time for an excel is really huge. I am spending hours to get the data, for each refresh. Any easier way to export data?

Thanks I am having issue with combine , I have two files in SharePoint folder oct-21 and dec-21 oct-21 has data from 2020 till oct and dec has only 2021 data ..when i combine both data duplication is happening for columns which are present in both files ..other than that other columns are showing correct data. Please assist on this...

What method would you use if you had to duplicate the Sample file into 2 seperate process because of the data structure and you wanted to merge and use the final combined process into each workbook?

Thank you ! Please help me. I want to combine multiple excel. I followed your but I have faced some error

Can the files have different data? I need to combine 4 files each having a few columns I need. Should I transform instead of Combine so that I can use the Sample file for edits on each file then combine?

I have a folder with 400k+ rows and this is very slow. I tried changing the file instead of the sample file, but they are both equally slow. Not sure if there's a better way to automate that?

I have to completely replace the files within the folder and it’s causing the PQ to break. Any tips for a solution? I believe my query is pointing to a specific file at the moment, so I will try it with the first file route. Looking for other suggestions if I can….

I have a problem to left-out merge two excel files from the same folder via Power Query. I merged but each row doubled. How to solve this? Thanks.

Hi Access Analytics, When I try to change share point files to share point contents, it asks for credentials and after putting it says credentials does not work. It works smoothly for SharePoint files? Any ideas why?

Combine and Transform gives me an error. I have 70 files to merge and get a few different parameters to pick from. Each file has 1-15 tabs or so. I made the structure the same, so it should be okay.

So how do we add headers like "Authors, tags, file size?" ?

Hey Wyn, i have my sharepoint folder synced to windows explorer and get my data from there, wont it work too? it worked for me but then my collagues tried to refresh the data, unfotunately it didnt succeed with message that the connection is under my name and not allowing her to refresh. she definitely have access to that folder as well. is that because of the getting daya from sync folder thing to explorer?

Tried this with a folder of Excel files, get an error message. “Unable to connect, We encountered an error while trying to connect. Details: file contains corrupted data”. All the files open correctly in Excel. Any suggestions?

It's a real struggle from SharePoibt for 900 files with a table with 100+ rows (100k data rows) It's rulling at 900 seconds. I had to incorporate vBA to make it an incremental update to a backup master ,. For only SP records that gave changed (which is not the same as file has changed).

Sir is it work for google sheet?

my pc directly opens the files application it does not ask to choose any path and neither iam seeing any excel file in those folders even though i have one

Hi, what if the files are kept in Sharepoint only for 3 months and I want the consolidate file to keep all data even though the first files will not be available anymore?