Using the Power Query Add-In - Microsoft Business Intelligence Tools for Excel Analysts - page 145

Advanced function example: Combining all
Excel files in a directory into one table
When building a basic function like a profit function, it's no big deal to start from a blank query and
enter all the code from scratch. But for more complex functions, it's generally smarter to build a
starter query via Query Editor, and then manipulate the M code to accomplish what you need.
For example, imagine you have a set of Excel files in a directory (see Figure 7-22). These files all contain
a worksheet called MySheet that holds tables of data. The tables in each file have the same structure
but need to be combined into one file. This is a common task that you've probably faced at one time
or another. Without a solid knowledge of Excel VBA programming, this task typically entails opening
each file, copying the data on the MySheet tab, and then pasting the data into single workbook.
Figure 7-22: Imagine you have the task of combining the data in all the Excel files in this directory into one table.
Power Query can make short work of this task, but it requires a bit of direction via a custom function.
It would be difficult for most anyone to start from a blank query and type the M code for the rela-
tively complex function needed for this endeavor. Instead, you can build a starter query via Query
Editor, and then wrap the query in a function.
Follow these steps:
1. On the Power Query tab, choose From File ➜ From Excel.
2. Browse to the directory that contains all the Excel files and select one of them.
3. In the Navigator pane (shown in Figure 7-23), select the sheet holding the data that needs to
be consolidated and then click Edit to open the Query Editor.
4. Use the Query Editor to apply some basic transformation actions.
For example, you can designate the first row as a column header and remove any unneeded
columns.
5. After you've applied the needed transformations, click the Advanced Editor button on the View tab.
The Advanced Editor window opens with the bulk of the code for your function already cre-
ated, as shown in Figure 7-24. Power Query hard-coded the file path and the filename for the
Excel file you originally selected. The idea is to wrap this starter code in a function that will
pass a dynamic file path and filename.