Excel Get & Transform is a re-branding of Power Query one of the three “power” tools now included in all current Excel versions.
Get & Transform’s main role is to prepare data tables for Power Pivot, enabling Power Pivot to create a data model from them. If you are interested in this aspect of Get & Transform I recommend that you read my Excel Power Pivot 2-minute overview article first. That puts the whole subject of how Get & Transform and Power Pivot work together into context.
Even if you have no wish to use Power Pivot, Get & Transform is also very useful when importing external data into regular Excel workbooks.
This article is an unedited lesson from one of the 35 short focused lessons in my Excel Expert Skills book/e-book that seek to empower Excel users with a complete mastery of the Get & Transform tool.
Leave me a comment if you find the article useful.
- Excel 365
- Excel 2019
- Excel 2016
- Last Updated: September 21, 2020
note
Confusing terminology
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.
Lesson11-1 Understand Excel Get & Transform and ETL
Excel 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 sidebar).
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. Data Modeling will be covered later, in: Session Twelve: Power Pivot, Data Modeling, OLAP and Business Intelligence.
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. You’ll learn more about queries later, in: Lesson 11‑3: Understand queries and connections.
- Automatically refreshed data: You can configure a query to automatically refresh an Excel table at a timed interval. You’ll learn how to automatically refresh data later, in: Lesson 11‑3: 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). You’ll learn more about pre-aggregating data later, in: Lesson 11‑18: 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 Unpivot Columns tool that you’ll learn about in: Lesson 11‑19: 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. You’ll learn about this later, in: Lesson 11‑31: Understand normal and de-normalized data.
This lesson is excerpted from the above book.
This is the only up-to-date Excel book currently published and includes an entire session devoted to the new Dynamic Arrays features.
It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
Lessons in Session 11
| Session Eleven: Create Get & Transform queries |
| Lesson 11‑1: Understand Get & Transform and ETL |
| Lesson 11‑2: Create a simple extract and load web query |
| Lesson 11‑3: Understand queries and connections |
| Lesson 11‑4: Move, remove, rename, filter and sort columns |
| Lesson 11‑5: Split delimited data |
| Lesson 11‑6: Understand data types |
| Lesson 11‑7: Create and use a linked data type |
| Lesson 11‑8: Add a linked data type field using a data card |
| Lesson 11‑9: Resolve geocoding errors in the geography linked data type |
| Lesson 11‑10: Understand Get & Transform data types |
| Lesson 11‑11: Specify data types |
| Lesson 11‑12: Understand steps and PQFL |
| Lesson 11‑13: Remove empty, error and top and bottom rows |
| Lesson 11‑14: Understand and work with null values |
| Lesson 11‑15: Transform date and time columns |
| Lesson 11‑16: Transform number columns |
| Lesson 11‑17: Add a custom calculated column |
| Lesson 11‑18: Create an aggregated data query |
| Lesson 11‑19: Unpivot aggregated data |
| Lesson 11‑20: Work with multiple queries |
| Lesson 11‑21: Create an append query |
| Lesson 11‑22: Understand Column From Examples |
| Lesson 11‑23: Use Column From Examples with selected source columns |
| Lesson 11‑24: Use Column From Examples to extract characters from strings |
| Lesson 11‑25: Appreciate the potential of Column From Examples |
| Lesson 11‑26: Understand primary and foreign keys |
| Lesson 11‑27: Link primary and foreign keys using XLOOKUP |
| Lesson 11‑28: Efficiently import data using a view |
| Lesson 11‑29: Understand linked table and pivot table refresh |
| Lesson 11‑30: Load a query directly into the PivotTable cache |
| Lesson 11‑31: Understand normal and de-normalized data |
| Lesson 11‑32: Create a simple two-table merged query |
| Lesson 11‑33: Create a five-table merged query |
| Lesson 11‑34: Create a merged query using fuzzy logic |
| Lesson 11‑35: Improve fuzzy logic results using a custom transformation table |
| Session 11: Exercise |
| Session 11: Exercise Answers |
Related Articles
Excel Power Pivot – 2 minute overview
Power Pivot causes great confusion. This simple 2-minute overview completely de-mystifies Power Pivot and its associated technologies.
Excel modern data analysis
This article describes a new way of working with Excel called “modern data analysis” enabled by the new Get & Transform and Power Pivot tools.
Duplicating tables to other worksheets
This article describes how to duplicate Excel tables to different workbooks, using copy and paste, formulas or the new Get & Transform feature.
How to consolidate data and automatically keep it up to date in Excel
This article shows several different ways of consolidating data in Excel, including the Consolidate tool and the new Get & Transform feature.