This lesson is excerpted from the Expert Skills book
This lesson as it is excerpted form the book: Learn Excel 365 Expert Skills with The Smart Method: Fourth Edition: updated for the Jul 2020 Semi-Annual version 2002.
This is the first introductory lesson in the Create Get & Transform Queries session. This lesson is designed to give an overview of Get & Transform (previously called Power Query) before the student embarks upon a series of 35 short lessons that provide an in-depth understanding of this remarkable tool.
Get & Transform is an advanced ETL tool. ETL is an acronym for Extract, Transform and Load.
Before Get & Transform was added to Excel, users had to import data into a worksheet and then transform the data inside Excel. The new Get & Transform tool enables you to import data (from one or more external sources) and then transform it before it is loaded into Excel (or a data model). This provides many advantages.
The diagram below should make it clear what is meant by the terms Extract, Transform and Load.
The ETL methodology
Extract: Most data that is analyzed with Excel doesn’t start its life in an Excel workbook but is imported from an external data source (often from a database or CSV file). Extract simply means moving this data from the external data source into the Get & Transform tool.
Transform: Extracted data often isn’t in a form that can be easily analyzed by Excel. There may be unwanted columns, badly named fields, badly formatted fields or corrupted data. The Get & Transform tool includes a vast array of features that enable you to clean your data before loading it into an Excel table or Pivot Table. This cleaning process is called Data Transformation (see info box).
Load: This simply means exporting the transformed data from the Get & Transform tool to its destination. A Get & Transform query can export (load) transformed data into an Excel table or into an Excel Pivot Table.
Get & Transform can also load data into a special construct called a Data Model. The Expert Skills book explains Data Modeling from first principles in a later session: Power Pivot, Data Modeling, OLAP and Business Intelligence.
In the world of IT, we are very fond of terminology.
The process that I describe as Data Transformation is often referred to using the following alternative terminology:
- Data Wrangling
- Data Cleansing
- Data Scrubbing
- Data Shaping
- Data Pre-Processing
- Data Munging
In this session I will exclusively use the term Data Transformation.
You can, of course, use the above terminology liberally in meetings. This will make you sound very important and help to confuse your co-workers.
Personally, I’d rather be referred to as a transformer than a munger, scrubber or wrangler.
Why is ETL better than ELT?
When you add and delete columns, change column headers, re-format columns and add calculated columns to Excel tables you are transforming your data. Excel users that do not understand how to use the Get & Transform tool use an ELT methodology (Extract from data source, Load into Excel and Transform within Excel). Here are some of the advantages in using Get & Transform to implement an ETL methodology:
- Re-usable and sharable queries: The Get & Transform tool generates a re-usable Query. This means that all of the Get & Transform actions that you define can be repeated to refresh the data in a table on your worksheet with a single click. The Expert Skills book fully explains queries in a later lesson: Understand queries and connections.
- Automatically refreshed data: You can configure a query to automatically refresh an Excel table at a timed interval. The Expert Skills book explains how to do this in a later lesson: Understand queries and connections.
- The ability to transform big data: Get & Transform does not share Excel’s limits (of approximately a million rows of data) so it can be used to transform big data (sending the result directly to the data model, 3D Map, or to an Excel worksheet after aggregation). The Expert Skills book has a later lesson that explains pre-aggregating: Create an aggregated data query.
- Better tools: Get & Transform has some advanced transformation tools that are not found in the standard Excel product (for example, the Expert Skills book uses the Unpivot Columns tool in a later lesson: Unpivot aggregated data).
- The ability to combine data: Get & Transform enables you to merge queries. This enables you to combine relational data from disparate data sources to create a de-normalized data extract. The Expert Skills book explores this subject in depth in a later lesson: Understand normal and de-normalized data.