Combine Multiple Files or Append All to one Table - Power Query a.k.a. Data Explorer for Excel

Have you ever wanted to combine MULTIPLE files into one main file? Use Data Explorer to combine or append data into one table from multiple files (txt, csv or other types of sources)

[ThePowerUser]

Hey Derik,

Thanks for watching the video! I haven't found an easy way yet to append multiple Excel files rather than doing the 1 by 1 combination. In those cases I'd prefer to use a SQL Statement called UNION ALL where you could combine (append) multiple Excel workbooks into 1 big long table.

I haven't tried the Append & Merge but it sounds pretty interesting! I'll try to do a little bit more of a research on that case and see if its feasible.

Hope this helps!

[DerikB]

Fantastic - I had been using append to do Excel table to excel table appends but this really shows PowerQuery's utility to do this using 'file explorer' type combinations. Just a question, have you found a way to do several appends from Excel tables in a single PowerQuery Query, or to do an append and a merge at the same time?

My tedious solution had been to do these one after the other in separate worksheet tabs using PowerQuery.

[emilyloh]

Hi, if i have different worksheets in a file and many of the files of the same format. I only want to consolidate one of the worksheet in that file. How do I go about doing that? thanks!

[ThePowerUser]

Glad you like it! so far I've tried with txt and csv files that worked great and also xls files but that didn't go well.

[wendylara]

But, if the files have different columns, the mismatching columns wont show up in the consolidated table, what can i do to bring all the columns?

[ThePerkinpop]

Hey mate,
Just a question when using Excel 2010 i cant seem to find the option to import a folder under the data tab do you know where I can find this using my version of Excel.

thanks in advance Terry

[Soulenergy]

Hola Mr. Power user, quick question: how can i get from my pretty extensive list to remove those text values that duplicate in the header? my combined file add up 100, 000 rows!

[Lukepetersen]

It is a great tool but doesn't appear to work as well as SQL Union All. I can't combine more than 1 .xlsx file either

[CElderton]

Please help!  This was very helpful, but I'm having trouble with one aspect of the file merge.  I have 2 data files.  One is a master file that I would like to update with new data, but not all of the entries in the master file will HAVE new data.  When I use this method of combining the files, all of the data entries that didn't have updates disappear.  I would like to be able to keep the old data and only update when there is an entry in the new data.  Does this make sense?  Is this possible???

[gopiprince]

Great, very usefull i have of about 2.4 GB of data with 8 csv files, it made my job easier

[CornerMeetJZ]

Nice video! I tried to combine only .xlsx files into one table using data explorer as you did in this tutorial, but data explorer only extracted the first .xlsx file. It seems that it only works for .csv files.

[mattmatt]

This method seems to work only with csv files. How do I make it work with xlsxfiles ?

[MrCarlold]

Hi Mr. Power User. I am willing import a lot of data on Excel. I have a lot o folders with many txt files each. My files are this type: Folder 1 --> A1.txt, Folder 2 --> B1.txt, B2.txt...B200.txt. It repeats up to Folder 15. Have you realized that it's an exhausted task? That's why I willing make this process automatic. How could I do that? Thanks from now!

[Soulenergy]

Thank you Mr, Power user, I was able to made it!, is there a way that multiple files or excel tables may feel this one table as their data grow in a daily basis?

[danjaeger]

Big step where do you FIND data explorer?