Understanding connections and queries

Before we begin, it is important for us to understand the typical data analysis process in Microsoft Excel and the tools you can use at each stage. Data modeling, which is the main topic of this book, is part of the process and can be done with the Power Pivot tool.

However, before data gets to the data modeling stage, we may have to shape and transform it to make it easier to analyze. Not all data comes in a format ready for the data model. There are situations where you need to remove unwanted characters, split or merge columns, transpose the data, or append or merge it to make it fit to be loaded into the data model.

Microsoft Excel has a dedicated tool for this called Power Query. Power Query is Excel’s data transformation and preparation engine. The tool allows you to connect to many data sources (including CSV files, the web, folders, and databases) and shape and transform the data and load it in different forms for further analysis...