{"id":43,"date":"2023-08-20T19:30:00","date_gmt":"2023-08-20T16:30:00","guid":{"rendered":"https:\/\/fsaeed.blog\/?p=43"},"modified":"2026-10-31T00:18:54","modified_gmt":"2026-10-30T21:18:54","slug":"power-query-combine-folder-excel-files","status":"publish","type":"post","link":"https:\/\/fsaeed.blog\/index.php\/2023\/08\/20\/power-query-combine-folder-excel-files\/","title":{"rendered":"Power Query: Combine a Folder of Monthly Excel Files in One Click"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Hello, tech enthusiasts! \ud83d\udc4b<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Every month, a new sales file arrives. You open it, copy the rows, and paste them under last month&#8217;s rows in a big master file. Then you do it again for the next file. And again. It is slow, and one wrong paste can break your whole report.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Today we will fix this with <strong>Power Query<\/strong>. Power Query is a tool inside Excel that collects and cleans data for you. Think of it like a recipe: you show Excel the steps one time, and it repeats them every time you ask.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">At the end of this post, you will have one table that holds all your monthly files. Next month, you will add the new file to a folder and click one button. That is all.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What you need<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Excel for Microsoft 365, Excel 2016 or newer (Power Query is built in)<\/li>\n\n\n<li>A folder with your monthly files, for example <code>C:\\Reports\\Sales<\/code><\/li>\n\n\n<li>Files with the same layout: same column names, same order, same sheet name<\/li>\n\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">For this example, our folder has <code>sales_2023_01.xlsx<\/code>, <code>sales_2023_02.xlsx<\/code> and <code>sales_2023_03.xlsx<\/code>. Each file has a sheet called <code>Sales<\/code> with the columns Date, Region, Product and Amount.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Connect Excel to the folder<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Open a new, empty workbook. This will be your master report. Then:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Go to the <strong>Data<\/strong> tab.<\/li>\n\n\n<li>Click <strong>Get Data<\/strong> &gt; <strong>From File<\/strong> &gt; <strong>From Folder<\/strong>.<\/li>\n\n\n<li>Choose your folder (<code>C:\\Reports\\Sales<\/code>) and click <strong>Open<\/strong>.<\/li>\n\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Excel now shows a list of the files in the folder. You do not see the data yet, only the file names. This is normal.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2: Combine and transform<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">At the bottom of the window, click <strong>Combine<\/strong> &gt; <strong>Combine &amp; Transform Data<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A new window opens. Excel asks you to pick a <strong>sample file<\/strong>. A sample file is like a model: Excel looks at it to learn the layout, then uses the same steps for all the other files. Choose the <code>Sales<\/code> sheet and click <strong>OK<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The Power Query Editor opens. You now see the rows from all three files in one table. Excel also adds a column called <code>Source.Name<\/code>. It shows which file each row came from. This is very useful, so keep it.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3: Clean the data once<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">While you are in the editor, fix small problems one time:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Click the small icon next to the <strong>Date<\/strong> column name and choose <strong>Date<\/strong>.<\/li>\n\n\n<li>Set <strong>Amount<\/strong> to <strong>Decimal Number<\/strong> the same way.<\/li>\n\n\n<li>Right-click <code>Source.Name<\/code> and choose <strong>Rename<\/strong> to call it <code>File<\/code>.<\/li>\n\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Every click is saved as a step on the right side, under <strong>Applied Steps<\/strong>. You can click a step to see the data at that moment. You can also delete a step if you made a mistake.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 4: Skip files you do not want<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Sometimes a folder has other files, like a PDF or a hidden temporary file. Temporary files start with <code>~$<\/code> and appear when someone has a file open. They can break the refresh.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To skip them, go back to the first query (the one with the file list). Click the <strong>Source<\/strong> step, then click the <code>fx<\/code> button next to the formula bar to add a new step. Type this formula:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>= Table.SelectRows(Source, each [Extension] = \".xlsx\" and not Text.StartsWith([Name], \"~$\"))<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This keeps only <code>.xlsx<\/code> files and removes the temporary ones. <code>Table.SelectRows<\/code> is like the filter button in Excel, but written as a formula.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 5: Load the table into Excel<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Click <strong>Home<\/strong> &gt; <strong>Close &amp; Load<\/strong>. Excel creates a new sheet with one clean table. Now you can build a PivotTable on it, add charts, or use formulas like <code>SUMIFS<\/code>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 6: Next month, just refresh<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">When the August file arrives, save it into the same folder with the same layout. Then open your master workbook and click <strong>Data<\/strong> &gt; <strong>Refresh All<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Power Query reads the folder again, finds the new file, and repeats all your steps. Your table now includes August. Your PivotTables update too, after one more <strong>Refresh All<\/strong> if needed. No copy, no paste. \ud83c\udf89<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Common problems<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>&#8220;The column &#8216;Amount&#8217; of the table wasn&#8217;t found.&#8221;<\/strong> \u2192 One file has a different column name. Open it and fix the header so all files match.<\/li>\n\n\n<li><strong>Refresh shows an error about a file in use.<\/strong> \u2192 Someone has a file open, so a <code>~$<\/code> file exists. Add the filter from Step 4, or close the file.<\/li>\n\n\n<li><strong>Dates look wrong (month and day swapped).<\/strong> \u2192 Right-click the Date column, choose <strong>Change Type<\/strong> &gt; <strong>Using Locale<\/strong>, and pick the locale of the source files.<\/li>\n\n\n<li><strong>You moved the folder.<\/strong> \u2192 Click the <strong>Source<\/strong> step in the first query and update the folder path.<\/li>\n\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Wrap-up<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You built a small machine. It collects all your monthly files, cleans them, and puts them in one table. You set it up once, and now each month takes a few seconds.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next, try adding a step that removes rows with an empty Amount, or group the data by Region with <strong>Transform<\/strong> &gt; <strong>Group By<\/strong>. Power Query has many buttons, and each one you learn saves you more time. Happy automating!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to join many monthly Excel files into one table with Power Query, and update it next month with one click.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[7,5,6,8],"class_list":["post-43","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-automation","tag-excel","tag-power-query","tag-reporting"],"_links":{"self":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/43","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/comments?post=43"}],"version-history":[{"count":1,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/43\/revisions"}],"predecessor-version":[{"id":90,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/43\/revisions\/90"}],"wp:attachment":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/media?parent=43"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/categories?post=43"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/tags?post=43"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}