Power Query: the Ultimate Data Transformation Tool

The most important update to Excel in the last 12 years has been Power Query. Power Query is a powerful data connectivity and data transformation tool. Its purpose is to enable report and dashboard builders that connect directly to the data they need, wherever it is stored, transform the data as desired, and have a repeatable process of generating updated reports and dashboards as the underlying data changes.

Much of what Power Query makes possible out of the box for the modern Excel user is transformations that used to be only possible via Visual Basic for Applications (VBA) code in Excel. There are daily, weekly, and monthly reports that were not possible to fully automate in earlier versions of Excel without using VBA but are now possible by using Power Query. Power Query makes it possible to build fully automated dashboards in Excel without writing VBA code or complex formulas.

In this chapter, we will get you well acquainted...