Importing data from files in a folder
One of my favorite Power Query features is getting data from multiple files in a folder with the same schema and loading it to a single Excel table. You can even specify any files--or types of files--to exclude, and since it's Power Query, update your results by refreshing the data. This facility can be handy if, for example, you need to generate monthly reports by gathering similarly structured data from multiple departments or teams.
Here's how to use it:
Create a new Excel workbook and choose Data => Get Data => From File => From Folder.
Browse to the folder containing the files you want to load data from, then click Open; if you want to import data from multiple folders, select the parent folder.
Power Query displays a list of the folder's files. Click the Combine button's dropdown arrow and select one of the options. For example, to combine the data and load it straight into Excel, choose Combine & Load.
Select a sample file in the Combine Files dialog box, which Power Query will use as the basis for its queries. Then select which data to import, place a check in the "Skip files with errors" checkbox, and click OK.
When you click OK, Power Query creates a query (and some helper queries) that combines the data.
What if you want to exclude some of the files?
If you only want to combine some of the files in the folder, you can specify which ones to exclude as follows:
Launch the Power Query Editor and navigate to the main query created in Step 4 above.
Click the Source step in the Query Settings pane
The Editor displays a list of the folder's files, which you can now filter. For example, you can filter by file name, extension, folder path, or the last modified date.
When you've applied the filters, choose Home => Close & Load to load the data to Excel.
I include a demo of combining files from a folder in my Foundations of Excel course, and you'll be able to find these tips and more in the Excel Cookbook Power Query chapter.