Combining Data from Multiple Sources

Summary

In this chapter, you have been introduced to one of the most common transformations in Power Query. Combining tables that have the same format will simplify your analysis and avoid the reporting complexities that tend to result in working with silos of separated datasets.

This chapter began with the basic transformations Append queries and Append queries as new, which enable you to combine two or more tables. It discussed the difference between the two methods and the importance of the Reference and Query Dependencies options for reviewing your transformation flow.

The second part of the chapter moved to real-life scenarios in which the number of tables and their names were unknown. You learned about Import from Folder, which combines tables from a folder and allows you to append the tables while keeping the context from their file names (available in the column Source.Name).

In this chapter, you have also learned how to append tables from multiple worksheets while keeping the original worksheet names as an additional context and ensuring that new worksheets will be appended to the query without further modification.

Finally, in this chapter you have learned about a few of the common pitfalls that are discussed in more detail in Chapter 10, and you have learned how to improve a query by using the M function Table.ColumnNames, which helps you avoid refresh failures when the name of the first worksheet is changed.

This chapter assumed that the appended tables share the same format—an assumption that made your job relatively easy—but in Chapter 4, “Combining Mismatched Tables,” you will examine common scenarios in which even slight variations in format between tables can make the combination of tables impossible without proper data preparation. You will find Chapter 4 crucial to obtaining everlasting wisdom from your newly acquired knowledge. With Power Query, it is not rare to combine power and wisdom.