EASY Trick to COMBINE Multiple Excel files into ONE with Power Query

Support Me & More: linktr.ee/benthompsonuk
How to combine the content of all files within a folder!
In this video we demonstrate how to quickly combine the data from multiple files into a single data set using Power Query... so no more manually copying to a master file!
Why would you want to combine data from multiple files? The particular scenario we look at is combining monthly data such as time stamped reports and the benefit of combining into one data set gives us the ability to visualise that data.
In this example we use Power Query within Excel, but the content in this video is the same and transferable to other applications that use Power Query such as Microsoft Power BI.
If you are new to the channel or watched out videos before, please consider subscribing and if you enjoy the video don't forget to give it a like!
Instructor Lead Excel Course ► essentialexceluk.com/products...
More Tutorials ► essentialexceluk.com/blogs/tu...
#powerquery #combine #tutorial #merge

[@EssentialExcel]

I hope you enjoyed this tutorial 😃 If you did please give it a LIKE... and don’t forget to SUBSCRIBE for even more content! For More 👉 kzbin.info

[@aoianalytics]

Worked like magic! Successfully used this method to join 13 files with a total of 120k plus rows. Thank you!

[@adamdigman3823]

Thank you!

[@EssentialExcel]

Welcome Adam! 🙂

[@thearchibaldtuttle]

Thanks for the great intro!

[@EssentialExcel]

Thanks Archibald! 🙂

[@simonsales]

Thank you! It was super helpful - when appending files the regular what - by Folder - it does created a lot of unnecessary steps.

[@alializadeh8195]

Thanx

[@LilianMedinaTaboada]

Hi there, I have uploaded two excel files with a manual formula as shown on your video, and the tables appear on the query table. On the second step, Applied Steps>Source>Insert Step After->I entered the formula "Binary.Combine(Source[Content])", press enter. I get the following error: The input couldn't be recognized as a valid Excel document. Details: [Binary]. How can I fix this? Thanks for the help.

[@julialewis8794]

His source files are .CSV files. It fails with .XLS files

[@israelmello2203]

looks like it works only with csv, I tried csv and worked but with xlsx did not work,, do u know why?

[@gnrramirez3909]

Do these steps apply only if you have the same data per file?

[@curioussoul123]

@essentialexcel I want to use this method, to combine two three files fetched using option of url paste in get data. I cant see the content column there, also the query is not working, can you suggest solution? Thanks in advance.

[@chidinmaonwuegbu5883]

I have 5different file about a sales data how do I combine this files together, because from what you did the files have common columns

[@garylillich]

Any example files to practice??

[@israelmello2203]

excelent video exactly what i was looking for except did not work form me.. did not open a new table = Binary.Combine(Source[Content]) is that right?

[@emmanueladeola5122]

Didn't work for me too

[@michaelp1856]

Thanks. The files I have contain some cells filled with a colour that I would like to retain in the combined file (not via conditional formatting). Is there a way?

[@thearchibaldtuttle]

No, it's just reading the data

[@stefifigueira598]

How to add hyperlink as the sample task has to the final outcome of combined Excel

[@Toralero]

I need to do this but have some complex logic I'd like to match certain rows from file 1 with info from file 2 based on some criteria..

[@josuechavez2070]

Do the files in the folder need to have a certain name ? Or can they be different names ? Or does name not matter? It will combine any file in there ?

[@Foodietheexplorer]

How to import along with formatting of source files.

[@Ujwaluday507]

Why cant i fetch the DATA source by using = Folder . files

[@shitalsingh4995]

Frome where you took contain???

[@Am39615]

when I try to copy and paste the file path , I get this error message "Expression.SyntaxError: Token Comma expected." any idea how to sort this?

[@thearchibaldtuttle]

make sure your path is inbetween double quotes. Like this "your path". Could be the reason for your message!?

[@yvesleclercq579]

please note, this does NOT work with xls files

[@upma9051]

Yeah not working

[@EssentialExcel]

Sorry this wasn't helpful for you, however video was specific to combining CSV files. Although similar solution is available for XLS, this is not ideal as you also have to deal with multiple tabs... therefore CSV is the suggested solution.

[@jonaswesley2658]

what if the column orders are different in 2 files?

[@shubhabratadey]

Still it would work. The order of columns do not matter till the time the Column Names are using the same spellings in each of the files. Hope it answers?

[@tutsecret499]

Can we have the data file so we can practice. How are you maximizing your screen, are using magnifier of something else

[@EssentialExcel]

Hello 😀. I am currently working on a dedicated Power BI course playlist (link below) which will revisit this topic including practice files 😀 kzbin.info/aero/PLYy3v7WkxwZd1y6Y8_iN1-fJfDVHk8j1c&si=jUq3UEouYs0tkZf7

[@dirkstaszak4838]

Nice, but I believe too many change data type steps

[@user-jc2gr4wg6d]

Hi, how about combining multiple excel files with multiple sheets into one?

[@EssentialExcel]

Hello. Although solutions are available, I would strongly suggest first converting your XLS and contained tabs to CSV (possibly using Power Automate) then combining the CSV files as required. I could look to create this Power Automate tutorial if of interest?

[@mayurpatil640]

Yeah do that please

[@SherryReggio]

I got an error when trying to combine

[@EssentialExcel]

Hello 👋 Are you able to provide more detail on the order you experienced?

[@422piyush]

Good content but quality of video is not good

[@williamarthur4801]

Has anyone had any problems with this method, I could not get it to work, yes it combined the binaries, but into one binary not a table, my usual method ; Table.TransformColumns( Source, {"Content", each Excel.Workbook(_) {[Name = "Table1"] }[Data] } ) Result in table to be expanded. Binary.Combine ( Source [Content] ) , just combined all binaries into a single binary ? so in the vid it worked as Table.Combine, any suggestions as to what i'm doing wrong?