Excel 2016 Bible

Chapter 38Working with Get & Transform

IN THIS CHAPTER

1. Learning what the Get & Transform feature can do
2. Using Get & Transform to import and transform data
3. Using Get & Transform to return summarized data
4. Merging queries
5. Getting a list of files

Get & Transform is a powerful tool that helps you import data from a variety of sources and then transform the data in a number of ways.

New

In the past, Get & Transform was available as an add-in, but only for “Pro” versions of Excel 2010 and Excel 2013. In Excel 2016, Get & Transform is fully integrated into all desktop versions of Excel. Get & Transform is also known as Get & Transform, and before that it was known as Data Explorer. You still see references to these names on the Web.

This chapter provides an introduction to Get & Transform and has several examples that demonstrate key elements.

Get & Transform: An Overview

Get & Transform is a data analysis tool that lets you connect to data sources and import data. It also provides some flexible tools to transform and refine your data. To get a feel for the usefulness of this feature, consider the following scenario.

Your company's server hosts data that is updated weekly. You use Excel to import a data table for a report and spend time performing actions such as these:

• Remove unneeded columns
• Filter the data to exclude non-U.S. data
• Use formulas to look up the region name for each state
• Use formulas to calculate a ratio using data in two columns

You need to repeat these ...