Modern Data Analytics in Excel

Chapter 5. Merging and Appending Data in Power Query

So far in Part I, you have learned various operations to transform the rows and columns of a single table using Power Query. However, data often comes from multiple tables, including sources outside of Excel. In this chapter, you will discover how to merge multiple files into a single dataset.

Because this chapter focuses on connecting to external files rather than tables inside the same workbook, start following along by opening a new workbook.

Appending Multiple Sources

Data often arrives in formats that require vertically stacking files together. For example, Figure 5-1 illustrates a common scenario where sales data is presented in separate tables for January, February, and March. In these instances, it’s helpful to merge them into a single source. This enables the computation of total sales for quarter 1, for instance.

Figure 5-1. A simple example of datasets to append

The append operation in Power Query facilitates this process.

Connecting to External Excel Workbooks

So far, this book has used Power Query for the manipulation of data sources within a workbook. However, the utility of Power Query extends significantly beyond this scope. It facilitates integration with a multitude of data sources, notably external Excel files and .csv files, which will be a primary focus of this chapter. The ch_05 folder in the book’s ...