Combine Multiple Excel Workbooks in Power Query

2015-02-25
Ken Puls
Categories: Excel, Power BI, Power Query

I got a comment on a previous post today, which made me realize I’d promised this but never posted it. So let’s look at how to combine multiple workbooks together in Power Query, providing they have the same format. Yes, it’s been covered before, (even linked to in the comments of the previous posts,) but I’m going to put my own flavour on it. By the time we’re done, you’ll see how similar it is to working with non-Excel files.

Background

For our example we’re going to assume that we have four (or more) Excel files which you can download here. I’ve stored in a folder called “Data”, which is a subfolder of the “Combine Workbooks” folder (more on why I store them in a subfolder a little later.)

Each file has a similar structure, which looks like this:

Notice that no one has bothered to set up a table style or anything, they are just raw worksheets of data. Having said that, they are consistent in the fact that

The data starts in row 5
Each files is set up across same number of columns
The column headers and data types are consistent across files

(Just as a quick note, if they DID have tables set up, that would be okay too. I’m just demoing that it isn’t necessary.)

The End Goal

The end goal we’re after is fairly common. We basically want to grab the data from each file, strip out the first 4 rows, and append the tables together (without repeating header info.) This will essentially create a nice data source that we can use in PivotTables, charts and other tools.

The Process

My preferred tool to combine multiple workbooks into one data source – where it used to be VBA – is most definitely Power Query today. And here’s the steps we need to put together to make it work.

Import a single workbook
Convert it to a function
Import all file contents (using our function)
Combine all the data

Step 2 does involve a VERY minor manipulation of M code today, but as you’ll see it’s quite easy.

Let’s Combine Multiple Excel Workbooks

Step 1: Import a single workbook

To begin we’ll go to the Power Query menu and choose:

From File –> From Excel –> Sales-July2014.xlsx
Select Sheet1 and choose to Edit it

You’ll now see your query showing in the Query Editor:

We’ll need to do a bit of cleanup here to get the data just the way we need it:

Home –> Remove Rows –> Remove Top Rows –> 4 –> OK
Transform –> Use First Row As Headers
Select InventoryID and SalesPersonID –> Transform –> Data Type –> Whole Number
Select Cost, Price and Commission –> Transform –> Data Type –> Decimal Number
Select Date –> Transform –> Data Type –> Date
Select Date –> Home –> Remove Errors

That last one might be a bit odd, but I like to do that to my date columns. The reason is that this protects me when I stack another table and it has headers. I know that converting text to a date format will throw an error, so I’m guaranteed that any subsequent header rows will be nuked out.

At this point we’ve got a nice tidy import that would look pretty good if we decided to land it in a workbook.

Next up…

Step 2: Convert it to a function

Converting our nice query to a function is actually SUPER easy. To begin, while still in the Power Query editor, we need to go to the View tab and click Advanced Editor.

When we do, we’ll see code similar to this, with the highlighted portion being the most important part:

Okay, now follow carefully: Right before the let statement at the very beginning, type:

(filepath)=>

The ()=> indicate to Power Query that this is a function, not a regular query. And the “filepath” is the name of a parameter that we want to pass to the function.

The second part is that we want to replace the entire hard coded file path highlighted in yellow in the image above – including the quotes – with the name of our variable. When we do, the lead three lines should look like this:

That’s all the code editing you need to do. Let’s finalize this. Click Done. At which point you’ll see this:

No too inspiring or exciting really, but it IS what you want. Final thing to do here is give the function a better name than Sheet1. I’m going to use “fnGetContents”. Choose your name, replace Sheet1 in the name box, then click File –> Close and Load.

If the query shows up in your Queries Pane saying “Load is Disabled”, don’t freak out. That’s just a really poorly worded message that is indicating it is only a connection. It will still work. 🙂

You’ll also notice that, even though we imported this file, NOTHING has landed in any worksheet. Huh?

Oddly enough, that’s as designed…

Step 3: Import all file contents (using our function)

All right, now it’s show time. Let’s make some magic happen. Let’s go get all of the files we need:

Go to the Power Query tab –> From File –> From Folder
Browse and select the folder that holds the data files
Click OK

You’ll now end up in the Query Editor and be staring at this:

Now, normally we’d be tempted to click the double arrows beside the Content header to combine them all… but that doesn’t work here. (It just returns the first file when we’re working with Excel files.) So this is where we need to resort to our function.

Go to Add Column –> Add Custom Column
Enter the following: fnGetContents([Folder Path]&[Name])
Click Enter
Right click the new “Custom” column –> Remove Other Columns

Almost done!

Step 4: Combine all the data

You should now be seeing:

Click the little double headed arrow, turn off the “Use original column name as prefix”, and click OK.

You should now have a nice table of data. Give it a name, click Close and Load and you’re done!

Final Thoughts

Working with Excel files is pretty easy once you realize how easy creating that function really is.

One caveat though… If you store your “consolidating” workbook in the same folder as your data files, your fnGetContents() function will throw an error. The reason for this is that your consolidation workbook will also get pulled in to the query, as well as the temp file version (prefixed with a ~). You’ll need to filter out both files.

67 thoughts on “Combine Multiple Excel Workbooks in Power Query”

Stan on February 27, 2015 at 9:01 am said:

Great article but what happens if you have similar files but maybe a few columns are different. Will it still combine the data?

Also the "you can download file here" link is not working. Goes to OneDrive but I only see white space with loading circle dots image towards the top.
Ken Puls on February 27, 2015 at 9:23 am said:

Hi Stan,

On the link, that's weird. I tested it before I set the blog live, as I've had these issues before. What I do is create the shortcode and make sure I can access it from a browser that I know 100% is not logged in to my OneDrive. It worked at publication time, and it's working for me now as well. Not sure why it wouldn't be loading for you.

With regards to the combination of files. If the workbook structure is different, and if nothing in the code triggers any critical errors, then yes, they'll still get combined. You may have a really odd output structure though... if columns are in a different order they will still get combined A to A, B to B, etc.. And if some sheets have more columns than other, they'll still get combined, but you'll have blank rows for the sheets that don't have the same data.

To be fair, this technique was intended to combine files of the same type with the same columnar structure, NOT varying ones. Having said that, providing there is some logic that can be identified, we could almost certainly build a routine to combine several files with varying structures into one solid table. It would just take a lot more work, and probably some custom M coding.
Pingback: Excel Roundup 20150302 « Contextures Blog
David on March 23, 2015 at 8:22 am said:

Hi
Great article, and thanks for sharing.
Is there a way to ask the user for a folder that contains the data at time of running or refreshing the query?

Thanks
David
David on April 28, 2015 at 10:33 pm said:

When I try using the "fnGetContents" command it doesn't work.
For the formula: "= Table.AddColumn(Source, "Custom", each fnGetContents([Folder Path]&[Name]))"

it returns that it can't recognize fnGetContents and asks if it is spelled correctly. (I've got a Swedish version and this is my translation)

Any hints?
Ken Puls on April 29, 2015 at 8:18 am said:

Can you check and make sure you followed through step 2 completely? It sounds like you missed the step of renaming the query to fnGetContents in that step...
Ken Puls on April 29, 2015 at 8:18 am said:

David, I will look in to this.
Ken Puls on April 29, 2015 at 8:05 pm said:

Wait... David... I already did look into this. Forgot to comment back! https://excelguru.ca/2015/03/25/prompt-for-a-folder-at-refresh/
David on May 7, 2015 at 3:58 am said:

Hi Ken,
Sorry, I thought I'd get a notification when you answered and I haven't had time to look into this.

I think my main trouble is that I haven't changed the name to fnGetContents. How sloppy of me. I'll do it again and hopefully it will work. I'll get back to you.
David on May 7, 2015 at 11:14 pm said:

Thanks for the hint Ken. Great!
David on May 8, 2015 at 4:30 am said:

Next question is: is it possible to do the same for .txt files or access databases?
Ken Puls on May 8, 2015 at 7:46 am said:

Absolutely it is. 🙂
Ed on November 18, 2015 at 4:43 pm said:

Hello Ken,

Can you please explain advantage of creating your own function versus using PQ inbuilt function Excel.Workbook([Content]) to combine workbooks?

I was able to do same with inbuilt function with one additional step (filter (null)) to remove blank rows.

Thanks in advance & keep up the great work your doing.
Ken Puls on November 19, 2015 at 8:30 am said:

The answer to this is "it depends". If you've already built a long routine in the past and just want to re-use it, then the function may help you get there faster. If the files need to be pre-processed before being combined, then the function is definitely the way to go. If you have 2 (or more) different types of source file that you can identify based on a file characteristic (date/time, file name, path, etc), then you could cook up a solution that pre-processes them using different steps to end up in the same output format before you combine them.

If you can work around this using filters and such though, by all means do it. Whatever works best for you is the best solution in the end. 🙂
Ed on November 19, 2015 at 2:33 pm said:

Good points Ken and thanks for getting back to me.

Yes, I enjoy the flexibility that PQ offers and your website is a great resource for exploring these options.
john on January 25, 2016 at 7:16 am said:

That is great. I am wondering if there is a way using the connection you outlined above to import directly into powerpivot as my combined data sets will have multiple millions of rows of data.

Thanks-
Ken Puls on January 25, 2016 at 9:35 am said:

Depends on the version of Excel you are using. If you're in Excel 2013 or higher, the Load To... dialog has a checkbox to load directly to the Data Model. Check that and it goes to Power Pivot.

If you're in Excel 2010... it's not officially supported, but https://excelguru.ca/2015/04/29/load-power-query-directly-to-power-pivot-in-excel-2010/
Zilexa on March 7, 2016 at 9:06 am said:

@Ken Puls Since Excel has received an update last week or week before, I now ALWAYS get the following order, even when I follow your guide all over again. Nothing is changed in my source files. Everything is exactly the same. But this error keeps showing:
" An error occurred in the ‘’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=Table"

I still get all content from all excel files. But the error shown is annoying. To me it just seems like a bug in Power Query introduced by Microsoft. Do you have any ideas?
Ken Puls on March 7, 2016 at 2:29 pm said:

Odd... I'm not seeing that issue here. What is the version of Power Query that you're running? (And which version of Excel?)
Zilexa on March 8, 2016 at 3:29 am said:

Windows 7 x64 Pro.
Excel 2013 x64 15.0.4797.1000 MSO (15.0.4797.1002) part of Office Standard 2013.
Power Query version 2.29.4217.1861.

I figured it out! The solution is mentioned here:
https://social.technet.microsoft.com/Forums/en-US/b95e10cc-fd5f-42f7-ad55-0a0bac15fadc/new-sp-custom-list-results-in-power-query-error-expressionerror-the-key-did-not-match-any-rows-in?forum=powerquery

But I had trouble interpreting it yesterday until now.

The issue (since a week):
(filepath)=>
let
Source = Excel.Workbook(File.Contents(filepath), null, true),
Inventory_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Inventory_Sheet)
in
#"Promoted Headers"

The 'problem' is with Source, for some reason Excel will create a new column called Index Number when I import the whole folder and it will lead to the error.

The solution (again, I didn't need to do this last week):
(filepath)=>
let
Source = Excel.Workbook(File.Contents(filepath), null, true),
Inventory_Sheet = Source{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Inventory_Sheet)
in
#"Promoted Headers"

Just replace [Item="Sheet1",Kind="Sheet"] with 0 and it will work flawlessly again! I spend 4 hours figuring this out. Really happy with this easy fix!
Keith Gold on March 24, 2016 at 5:58 am said:

Hi there. I recently started with power query. I keep on getting duplicate data. Even when I try less workbooks (sheets).
Ken Puls on March 29, 2016 at 1:30 pm said:

Are you filtering to only one of the items in the "Kind" column? You should choose either Sheet, DefinedName or Table. The reason is that named ranges can live inside tables and worksheets, and tables live in worksheets. If you don't filter here, you're bound to get duplicate data. (FYI, we actually cover this on pages 47-50 of M is for Data Monkey: http://excelguru.ca/monkey)
Dave Pitts on May 10, 2016 at 9:54 am said:

Great Post

Just what I Needed
Pingback: Filenamen beim Verzeichnisimport mit Power Query integrieren | Linearis :: BI für die Fachabteilung
Erik on July 27, 2016 at 7:04 am said:

Hi,
Thanks for the explanation. It works really well.
One additional question:
Now all my data is in one big table and I can't recognize anymore from which file it originated. For instance with monthly updates you want to analyse all together to analyse the differences.
How can you add a column with the month belonging to that record?
In the folder list, I already extracted the month from the file name. Now I want that to be a variable when using the fnGetContents function.
Ken Puls on July 27, 2016 at 7:29 pm said:

Hi Erik,

Could you take this one to the Power Query forum at http://www.excelguru.ca/forums ? I think that's probably a better place to answer it. Thanks!
Jack on September 29, 2016 at 12:58 am said:

This is an amazing post. This saved me and my team a lot of time. Thank you very much!
BatTodor on November 6, 2016 at 4:31 am said:

Hi Ken,
Great post for merging workbooks.
I have (probably stupid) question: Is it possible to expand number of columns after each merged workbook?
What I mean?
In original workbooks I have 9 columns and variable rows (more than 80). The structure of each workbook is like this:
Store | Address of store | Data for Monday | Data for Tuesday | Data for Wednesday | Data for Thursday | Data for Friday | Data for Saturday | Data for Sunday |
I want to remove second column and transpose information, because to have dates on first column and list of stores on other columns.
The number of stores varies.
Based on this post I was able to merge all workbooks in one database, but still not able to expand it depends of number of stores. Every new store can be in any position. For example:
Workbook 1
Store | Address of store | Date01 | Date02 | Date03 | Date04 | Date05 | Date06 | Date07 |
Store 1 | Address of store 1 | 12 | 14 | 16 | 18 | 20 | 22 | 24 |
Store 2 | Address of store 2 | 13 | 15 | 17 | 19 | 21 | 23 | 25 |
Store 3 | Address of store 3 | 22 | 24 | 26 | 28 | 30 | 32 | 34 |
Store 4 | Address of store 4 | 23 | 25 | 27 | 29 | 31 | 33 | 35 |

Workbook 2
Store | Address of store | Date08 | Date09 | Date10 | Date11 | Date12 | Date13 | Date14 |
Store 1 | Address of store 1 | 32 | 34 | 36 | 38 | 40 | 42 | 44 |
Store 2 | Address of store 2 | 33 | 35 | 37 | 39 | 41 | 43 | 45 |
New Store1 | Address of New store1 | 22 | 24 | 26 | 28 | 30 | 32 | 34 |
Store 3 | Address of store 3 | 42 | 44 | 46 | 48 | 50 | 52 | 54 |
Store 4 | Address of store 4 | 43 | 45 | 47 | 49 | 51 | 53 | 55 |

Workbook 3
Store | Address of store | Date15 | Date16 | Date17 | Date18 | Date19 | Date20 | Date21 |
Store 1 | Address of store 1 | 42 | 44 | 46 | 48 | 50 | 52 | 54 |
Store 2 | Address of store 2 | 43 | 45 | 47 | 49 | 51 | 53 | 55 |
New Store1 | Address of New store1 | 32 | 34 | 36 | 38 | 40 | 42 | 44 |
Store 3 | Address of store 3 | 52 | 54 | 56 | 58 | 60 | 62 | 64 |
New Store2 | Address of New store1 | 72 | 74 | 76 | 78 | 80 | 82 | 84 |
Store 4 | Address of store 4 | 53 | 55 | 57 | 59 | 61 | 63 | 65 |

Merged database Date | Store 1 | Store 2 | Date01 | 12 Date02 | 14 Date03 | 16 Date04 | 18 Date05 | 20 Date06 | 22 Date07 | 24 Date08 | 32 Date09 | 34 Date10 | 36 Date11 | 38 Date12 | 40 Date13 | 42 Date14 | 44 Date15 | 42 Date16 | 44 Date17 | 46 Date18 | 48 Date19 | 50 Date20 | 52 Date21 | 54 need to look like this:
NS1 | Store 3 | NS2 | Store 4 |
| 13 | | 22 | | 23 |
| 15 | | 24 | | 25 |
| 17 | | 26 | | 27 |
| 19 | | 28 | | 29 |
| 21 | | 30 | | 31 |
| 23 | | 32 | | 33 |
| 25 | | 34 | | 35 |
| 33 | 22 | 42 | | 43 |
| 35 | 24 | 44 | | 45 |
| 37 | 26 | 46 | | 47 |
| 39 | 28 | 48 | | 49 |
| 41 | 30 | 50 | | 51 |
| 43 | 32 | 52 | | 53 |
| 45 | 35 | 54 | | 55 |
| 43 | 32 | 52 | 72 | 53 |
| 45 | 34 | 54 | 74 | 55 |
| 47 | 36 | 56 | 76 | 56 |
| 49 | 38 | 58 | 78 | 59 |
| 51 | 40 | 60 | 80 | 61 |
| 53 | 42 | 62 | 82 | 63 |
| 55 | 44 | 64 | 84 | 65 |

Could you help me?
Ken Puls on November 6, 2016 at 7:51 am said:

Hi BatTodor. I'm just heading out to a conference for a week. It's probably best to post this question in our forum at http://www.excelguru.ca/forums. You can upload a sample workbook and someone can help you through the issue.
Matador on November 22, 2016 at 8:50 am said:

Hello, Ken. This seems like the exact solution that I need. I am getting an error with the filepath in Advanced Editor.
My first line before let is:
(Y:\Clients\etc)=>
Then the filepath is copied to the Source line as instructed.
At the bottom I get the error, Token Comma expected. When I click Show error, it highlights the ":" after the Y in the filepath. This is a network folder location I am accessing from a local install of Excel 2016. Any ideas on how to resolve this error?
Ken Puls on November 22, 2016 at 12:30 pm said:

Matt, I'm guessing that you didn't surround your file path with quotes, so power query is trying to interpret the colon as a literal character.

Try ("Y:\Clients\etc")=>
Mette on January 30, 2017 at 2:17 am said:

Hi Ken,

Great article.

I was hoping you could help me solve my problem.

I want to combine multiple Excel workbooks just like you. BUT I have five sheets in each workbook which I want to combine - and after that I would like to do the same to all of the other files in my folder and combine all the workbooks.

I have been trying to create a function that does the transformations in each sheet and combines them afterwards, but I can't quite figure out how to do it...

So... Instead of having a function that just cleans up one sheet, I need to create a function that cleans and combines the sheets in the workbook - right? And afterwards I can proceed to step 2.

I hope you're able to help me...

Mette
Ken Puls on January 30, 2017 at 8:28 am said:

Hi Mette,

Can you post your question in our forum at http://www.excelguru.ca/forums ? Thanks!
Igor Golubochanski on March 19, 2017 at 7:26 am said:

Hi Ken,

This solution will work in folders that locatad on SharePoint ?

Thanks,

Igor
Ken Puls on March 19, 2017 at 2:59 pm said:

It will, although you connect to your folder differently.
Kyle on March 24, 2017 at 12:20 pm said:

After clicking the double arrow in the Custom column, my new data table is created, which is great, thanks! However, the columns lose their original order and are now sorted alphabetically. Did you find that too? Is there a workaround? I'd really like to keep my original column order. Thanks!
Ken Puls on March 26, 2017 at 10:25 am said:

Honestly, I haven't noticed. When I feed into the data model, this is rarely important to me.

You should be able to reorder the columns inside Power Query though.
luis gomez on March 27, 2017 at 12:58 pm said:

Hi, this is great I have been using it for a while and has saved me lots of trouble. many thanks for posting!.

I have run into an issue, within the folder we have created a sub-folder where we store copies of the source files periodically, sort of an "archive" of snapshots in time and now I keep getting duplicated data, is there a way to restrict the (filepath)=> function only to the current folder and not the sub-folders?

thanks again
Ken Puls on March 27, 2017 at 2:34 pm said:

Hey Luis,

You bet. In step 3, click the arrow at the top of the Folder Path column and filter to remove the subdirectory.
Andrea on April 27, 2017 at 12:33 pm said:

What should I do, if I need to combine 89 workbooks into one sheet -but only the second sheet of each workbook. In each workbook are 2 sheets. The second sheet has in each workbook has the same front row, the same number of columns, but various number of rows.
Moreover, is it a problem if the second row of sheets is empty?
Thank you
Ken Puls on April 27, 2017 at 8:56 pm said:

Andrea, that shouldn't be an issue. The varying number of rows is not relevant.

This can still be done, but it's a LOT easier if you have a current version of Power Query, as we can use the one-click combine method to combine the workbooks. When prompted, we can choose Sheet2 as the template, and it is pretty much good to go.

You can read more about the new method for working with Excel workbooks here: https://excelguru.ca/2016/12/22/combine-excel-files/
Claudine Beunens on August 1, 2017 at 3:57 am said:

hi,

my files are stored on a Sharepoint
I see Web.Contents in the code, instead of File.contents, so I have following

Source = Excel.Workbook(Web.Contents(filepath)),

should that also work? I have an error in my custom table and wonder if that is the reason

Kr Claudine
Ken Puls on August 1, 2017 at 10:49 am said:

Hi Claudine,

No, you actually need a Sharepoint connector. I've picked this up on the forum: https://www.excelguru.ca/forums/showthread.php?8135-Power-Query-Merge-data-stored-in-separate-files-in-separte-folders
Claudine Beunens on August 2, 2017 at 5:26 am said:

Thank you Ken, I merged 3 files till now, with the initial solution of this post
I have error for 1 file, it seems that error is coming from a column that isn't found

the error is not coming when I work with the solution of Importing files from
folder, in combination with custom column Excel.Workbook([Content])
Craig Calvert on September 8, 2017 at 5:56 am said:

How do you handle an issue of multiple workbooks all only have 1 sheet, but the sheet being named different each time?
I am downloading reports by year and the database names the output sheet in each workbook whatever the file name is?

Also it initially puts them out in CSV format with no commas but broke out into 7 columns. PQ does not like that at all so I converted all to .xlsx
Ken Puls on September 8, 2017 at 8:32 am said:

If I were doing multiple workbooks with one sheet that changes, I'd do this:
-get data from folder
-Add Custom Column using =Excel.Workbook([Contents])
-Expand that column
-Filter to worksheets
-Expand the Data column

At that point you should be good to go.
Spangamamman on September 12, 2017 at 12:13 pm said:

This is great tutorial for a beginner like me, but I can't run it all through, please see below where I run into “error”.

First, I work in Excel 2016 through Office 365, ie I don´t have Power BI which leads to that I can´t exactly follow these steps but I work my way around most of it. I believe that I should be able to run the complete example still, OK?

"Step 2, Convert into functions" turns up like this. I am supposed to exchange the hard codet file path with "filepath". My result is just like the example BUT, see the red text at the end :

Källa = Excel.Workbook(File.Contents("C:\Users\thoma\Documents\Kajsa\Övning excel\Power Query data\Sales-Jul2014.xlsx"), null, true),

Is it possible to describe why I get that and what I should do? I have tried different ways o solve it, but at the end, I get error in step 3:

Go to Add Column –> Add Custom Column
• Enter the following: fnGetContents([Folder Path]&[Name])
• Click Enter
• Right click the new “Custom” column –> Remove Other Columns
As it is, since I don´t have Power Pivot I don´t have the exact text above. None the less, I can´t run "the magic" and combine all the data. When I – my translation – “call for a designed function” – I create a new column with the text “error” in the cells below. I get different choices in the pop-up regarding drop-down by the text filepath, but I don´t understan what they stand for and I have not succeded “by chance”.

Thank you very much, is someone can help me out here!
Alex on October 6, 2017 at 11:52 am said:

Ken, how can I add the name of the workbook of each file in a column??
Thanks in advance
Ken Puls on October 10, 2017 at 10:02 am said:

Hey Alex, you should try this one instead as the function has been re-written to make it much easier: https://excelguru.ca/2016/12/21/new-combine-binaries-experience/
Ken Puls on October 10, 2017 at 10:03 am said:

Hey Spangamamman,

Can you post this in our forum at http://www.excelguru.ca/forums ? Then we can see what you're doing, and what may be going sideways on you.
SDM on January 24, 2018 at 12:28 am said:

Thank you sooo much for this blog entry and the amazing step-by-step guide with visuals! I have been trying to do this for JSON files via Power BI for over 3 weeks now. Finally spending 10 minutes following your instructions solved my problem! I can finally meet my deadline. Thank you soo very much!
This blog goes straight to my most treasured tips & tricks links list.
Ken Puls on January 24, 2018 at 10:37 am said:

Awesome, glad it helped!
Johan on February 26, 2018 at 7:20 am said:

Hi Ken,
I've learned to add files of a folder in my Power Query, but I hope you could help me to add several folders in my Power Query.

For example, I have several folders like (Sales 2016, Sales 2017 y Sales 2018), each one contain 12 files with report monthly.

How can I consolidaed these three folders in one unique Power Query's table?
Ken Puls on February 26, 2018 at 6:10 pm said:

Hi Johan,

Just place all the annual sales folders into a master Sales folder (so that they are subfolders of the Sales folder). Then target the top level folder for the combination. It will automatically pick up all the subfolders as well.
Yoke-Yin Purcaro on February 12, 2019 at 8:56 pm said:

Hello,

It seems like this steps will solve the problems I have. I basically have a folder and a sub folder. Let's call this folder "OTIF" and the sub folder calls "Reason" . There will be report of OTIF in the folder and reason workbook in the "Reason" sub folder. I have a query that merge these two tables together. So, every month, I will save the OTIF and Reason excel files in the folders. So, If I follow your steps, would it work? Thanks.
Ken Puls on February 14, 2019 at 10:05 am said:

Hi Yoke-Yin,

When you point to a folder, it also pulls in all files from any subfolders. So probably better to point to the subfolder itself to avoid confusion.
Thanh Nguyen on May 8, 2019 at 6:35 pm said:

Ken,
Thank you for the great post. This is exactly what I need. I just have a couple of clarification questions:
* All of my files in the folder will have 2 worksheets each. I only need the data from sheet 2. Do I need to add in any extra step to make sure that it only pull data from sheet 2?
* Every month, I will add a new file to the folder. PQ will get the data from the new file given that I set everything up correctly?
* I have two different reference tables that I'm hoping to merge to the combined data. Does it mean I need to merge these reference tables before converting the file to function?

Thanks, Ken.
Ken Puls on May 13, 2019 at 3:15 pm said:

Hi Thanh,

It will work, but you'd be better off to just use the new combine files experience now: https://excelguru.ca/2016/12/22/combine-excel-files/

Hope this helps!
Martin on May 29, 2019 at 11:35 am said:

I will try your method so that I can understand how it works.
My question is why?
I just put the files in a folder and pointed power query to the folder to import the files. I let it do it's thing and then made some small changes to the 'Transform Sample File' and the last step in the result file. Then changed type on the columns and boom! ... done. I should be able to put any new files into the folder to add to the data-set. As long as the formatting doesn't change in the source file of course.
I use this method all the time with .csv files that I export from our patient management system each week and month.
Ken Puls on May 29, 2019 at 5:01 pm said:

Why? Because this blog post is over four years old, and the experience you're describing didn't exist. (I.e. there is no reason to do this today.)

🙂
Vrinda on July 3, 2019 at 6:46 am said:

Hi,
Thanks, the post was extremely helpful. I wanted to check if there is number of files (max) limit from a folder that is set by Power BI as I plan to make an iterative folder to append every month’s file. Also, if additional columns are to be added in all of the source files, will that affect the original pbix refresh?

Many thanks. Vrinda
Ken Puls on July 3, 2019 at 8:01 am said:

Hi Vrinda,
I'm not aware of any limit, but after you hit a large number of files, you will see it starting to slow down. (Each time you run it, it does a full refresh. So if you grow to 10,000 files, it will process all of them each time.)
Pingback: Chris Webb's BI Blog: Creating M Functions From Parameterised Queries In Power BI Chris Webb's BI Blog
James on April 28, 2020 at 7:11 am said:

Hi Ken,

I know this post is rather old but I'm wondering if you can help me, I've followed the steps but I am getting the data from my first file repeated 3 times (which is the number of files in the folder I am looking at), any ideas how I can fix this?
Thanks!
Ken Puls on April 28, 2020 at 8:18 am said:

Hi James,

Can I suggest that you post this in our forums at https://excelguru.ca/forums instead? You'll be able to get more targeted help there.

Thanks!
Hartmut on July 31, 2020 at 1:17 am said:

After some playing around with various Power Query steps from above, eventually I ended up with a powerful one liner that does pretty much the same thing'n:
addSheet1Tables = Table.AddColumn(Source,"Sheet1", each Table.PromoteHeaders(Excel.Workbook( _ [Content]) {0}[Data]) ),

// Example Query: Combine Files from Folder, use First Sheet from each File
let
//FolderPath =GetValue("FolderPath"),
FolderPath ="C:\Users\gruehar\Documents\TestFolder",
Source = Folder.Files(FolderPath),
// add file filtering here if required

// important the underscore sign in front of [Content], {0}[Data] selects the data from 1st sheet
addSheet1Tables = Table.AddColumn(Source,"Sheet1", each Table.PromoteHeaders(Excel.Workbook( _ [Content]) {0}[Data]) ),
selectColumns = Table.SelectColumns(addSheet1Tables ,{"Name", "Date created", "Sheet1"}),

// file content specific M-code
expandSheets = Table.ExpandTableColumn(selectColumns, "Sheet1", {"Column1", "Column2"}, {"Column1", "Column2"})
in
expandSheets
Pingback: Make Power Query Prompt for a Folder at Refresh