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 actions every week because the imported data isn't precisely the way you need it. Getting the imported data into a usable form could easily take 30 minutes or more.

Using Get & Transform, you can perform these actions one time, and the software will remember what you did. Next week, simply refresh the data table, and all your actions will be applied automatically to the new data.

Data Source for Get & Transform

The Data Get & Transform New Query command expands to display a variety of options for retrieving data (see Figure 38.1). These data sources include web pages, Excel workbooks, text files, folders, and databases.

When you select a data source, Excel evaluates the data and displays the Navigator dialog box. This dialog box contains a list of all tables that are available. When you click a table name, you see a preview. Click Load to work with that table in the Query Editor.

The remainder of this chapter presents some examples of using Get & Transform.

Example: A Simple Query

This section contains a hands-on demo of using Get & Transform to bring in data from a table contained in an Excel workbook. You'll see how to specify the data, eliminate unneeded columns, rearrange the order of the columns, and add a calculated column.

Choosing the data source

When you use Get & Transform with an Excel workbook as the data source, the workbook file doesn't need to be open. Start with a new workbook, and then choose Data Get & Transform New Query From File From Workbook. Excel displays the Import Data dialog box, which you can use to locate the tucson weather.xlsx workbook file. Select the workbook and click Open.

Next, you see the Navigator dialog box, shown in Figure 38.2. The dialog box lists two items: the table (named Table1) and the worksheet (named Sheet1). Select Table1, and you see a preview of the data. You could choose Sheet1, but then the data would consist of all information in the worksheet (including the title row, which is not part of the data).

Select Table 1 and click Edit. Excel displays the Query Editor window, shown in Figure 38.3. The Query Editor is used only for manipulating the data that will be returned to Excel. The Query Editor looks a bit like Excel and has a similar tab and Ribbon user interface.

Removing extraneous columns

The table has 23 columns of data, but we're interested only in these four columns: Date, Max Temperature, Min Temperature, and Precipitation.

Choose Home Manage Columns Choose Columns, and Excel displays the Choose Columns dialog box shown in Figure 38.4. Place a check mark next to the four column names to keep, and click OK. The Query Editor now shows only those columns.

Next, right-click the Max Temperature column header, and choose Move Right from the shortcut menu. This action puts the Max Temperature column after the Min Temperature column.

Importing the data

The next step is to bring the data into your workbook. Choose Home Close and Load, and Excel inserts a new worksheet and displays the data in a table. Figure 38.5 shows the data, along with a chart that I created.

Modifying the query

In this step, we modify the query to show a new column: Temperature Range. This is the difference between the Max Temperature and the Min Temperature. We could add a new calculated column to the table, but it's better to modify the query rather than the results. Then, if the query is refreshed, the calculations are made before the data is updated in the workbook.

Notice that when the table is selected, Excel displays the normal Table Tools context tab, plus a Query Tools context tab. Choose Query Tools Query Edit Edit, and the Query Editor appears.

In the Query Editor, choose Add Column General Add Custom Column. Excel displays the Add Custom Column dialog box shown in Figure 38.6.

Change the new column name to Temperature Range, and enter this formula:

[Max Temperature]-[Min Temperature]

Note that you can double-click items in the Available Columns section to add them to your formula.

Click OK, and the Query Editor displays the new column, with the calculation for each row. Next, right-click the Temperature Range column header, and choose Move Left from the shortcut menu. This action puts the new column to the right of the other temperature columns.

Choose Home Close and Load, and Excel updates the query results in the workbook.

How Your Actions Are Recorded

When you work in the Query Editor window, Excel displays the Query Settings task pane. Use View Show Query Settings to toggle the display of this task pane. Every action that you perform is a “step” that's listed in the Applied Steps section. Figure 38.7 shows the task pane for the example in the previous section. Notice that six steps were performed:

1. Source: Choosing the source file
2. Navigation: Choosing the table in the source file
3. Removed Other Columns: Removing unwanted columns
4. Reordered Columns: Moving the Max Temperature column to the right
5. Added Custom: Adding the new column to calculate the temperature range
6. Reordered Columns1: Moving the custom column to the left

When you click one of these steps, you see the data after the action was taken. Steps that have a “gear” icon to the right can be modified. For example, if you double-click the Added Custom step, you'll see the Add Custom Column dialog box, which you used to specify the formula for the calculated column. If necessary, you can use this dialog box to modify the formula or give the column a different name.

You may have noticed that the Query Editor does not have an Undo command. If you make a mistake while editing a query, just delete the “step” that was recorded. Right-click the step in the Applied Steps list, and then choose Delete.

When you create a query, each step is converted to a Get & Transform formula and saved. So when you refresh a query, Excel executes each formula before returning the data to your worksheet.

If you're curious, you can view these formulas in the Query Editor. First, make sure the Formula bar is visible. In the Query Editor, choose View Show Formula Bar. Then click one of the items in the Applied Steps section of the Query Settings task pane, and the ­formula appears.

To view all the formulas for a query, choose Home Query Advanced Editor.

Example: Returning Summarized Data

The example in this section performs a query and returns a summary of the data. The source data is in a CSV text file and consists of 400 rows with three data fields: Employee name, Location, and Sex. Figure 38.8 shows a portion of the file. The goal is to create a query that returns the count of employees by location and sex.

Start with a new workbook and choose Data Get & Transform New Query From File From CSV. In the Import Data dialog box, locate the employee list.csv file and click Import. The file is displayed in the Query Editor (see Figure 38.9).

Notice that the column headings are not recognized. Rather, Get & Transform displays generic column names. This is common and is easily fixed: choose Transform Table Use First Row As Headers.

Summarizing the data consists of creating groups of rows — a group for Location and a group for Sex. Choose Transform Table Group By, and Excel displays the Group By dialog box. Then follow these steps:

1. In the Group By field, select Location.
2. Click the + icon (next to Group By) to add another Group By field.
3. In the second Group By field, select Sex.
4. The proposed New Column Name, Count, is fine, so don't change it.
5. The Operation, Count Rows, is also fine, so don't change it.
6. The Column field is not relevant for counting rows, so leave it blank.
7. Click OK. The Query Editor displays the count for each Location and Sex combination.

The data would be easier to read if it were sorted. Click the drop-down arrow in the Location column header, and choose Sort Ascending. Then click the drop-down arrow in the Sex column header, and choose Sort Descending (which puts Male before Female).

Choose Home Close Close & Load to return the data to your worksheet. Figure 38.10 shows the data summary in the worksheet.

Another option, of course, would be to import all the data and then create a pivot table to summarize the information. Pivot tables are more flexible, but creating a query to summarize the information makes it easy to update when the employee list is updated.

This example used counting, but the Group By dialog box supports other operations, such as Sum, Average, Min, and Max.

Example: Transforming Data from a Web Query

This example uses a web page as the data source and demonstrates how to perform some data transformations. The goal is to get a refreshable list of the top 10 highest grossing movies for the week. This information is provided by the Internet Movie Database (IMDb).

Start with a new workbook and choose Data Get & Transform New Query From Other Sources From Web. When prompted for a URL, type the following and click OK:

http://www.imdb.com/chart

As shown in Figure 38.11, the Navigator dialog box displays three items for this URL. Click Top Box Office, and you'll see a preview. It looks good, so click Edit to display the Query Editor. The list of movies, of course, will be different from those shown in the figure.

The query returns six columns. The first and last columns are empty, so you can select the two columns and remove them using Home Manage Columns Remove Columns. Or choose Home Manage Columns Choose Columns and remove them using the column list view.

Separating the year from the movie title

The four remaining columns are shown in Figure 38.12.

Notice that the year is included with the movie title. To put the year into a separate column, perform these steps:

1. Click the Title column header and choose Transform Text Column Split Column By Delimiter. Excel displays the Split Column By Delimiter dialog box.
2. Choose –Custom– for the delimiter, enter an open parenthesis character, and choose the option labeled At the Right-Most Delimiter.
3. Click OK to close the dialog box. A new column is created by splitting the Title field at the open parenthesis character.

Note that the closing parenthesis is still present in the new column. To remove the closing parenthesis, follow these steps:

1. Select the column and choose Transform Any Column Replace Values. Excel displays the Replace Values dialog box.
2. In the dialog box, enter a closing parenthesis character as the Value to Find and leave the Replace With box empty.
3. Click OK. The closing parentheses are removed.
4. The years are text (not values), so select the column and choose Transform Any Column Data Type Whole Number.

Splitting the column resulted in new column names. Change the column headers to Title and Year. An easy way to change a column heading is to double-click the name and enter new text.

Fixing the dollar values

Notice that the Weekend and Gross columns are not real values. We need to remove the dollar signs and the “M” character from each entry:

1. Click the Weekend column header, press Ctrl, and then click the Gross column header.
2. Choose Transform Any Column Replace Values, and replace the dollar sign character with nothing.
3. Repeat that command, but replace the “M” character with nothing.
4. The result is text, so use Transform Any Column Data Type Decimal Number to convert the entries to decimal numbers.
5. Select the Weekend column heading and choose Transform Number Column Standard Multiply. Excel displays the Multiply dialog box.
6. Enter 1000000 and click OK.
7. Repeat the same operation using the Gross column.

Figure 38.13 show the Query Editor after making these changes.

Importing the data

To finish up, choose Home Close & Load. Excel inserts the data into a table on a new worksheet.

Figure 38.14 shows the worksheet after applying numeric formatting to the Weekend and Gross columns.

---

Example: Merging Two Web Queries

In this example, I demonstrate how to merge two web queries to produce a single table of results. The goal is to generate a list of U.S. states and for each state to show the population and the year it became a state. This information is available on Wikipedia, but only in two separate tables.

Merging two web queries is certainly not the most efficient way to generate such a simple table. I chose this demo because it's easy to understand but still demonstrates the concept.

Performing the first web query

The first step is to get the data that shows when each state joined the union. Start with a new workbook and perform the following steps:

1. Choose Data Get & Transform New Query From Other Sources From Web. Excel displays a dialog box where you can enter the URL that contains the data.
2. Enter the following URL and click OK:

   http://simple.wikipedia.org/wiki/List_of_U.S._states

   Excel displays a Navigator dialog box that lists all data sources found at the URL.

3. Select Table 0, and click Edit. The Query Editor appears (see Figure 38.15).

   

4. Remove the Abbreviation and the Capital columns. These columns are not needed.
5. Click the Became a State column, and choose Transform Text Column Split Column By Number of Characters. We want only the year, so we will remove the month and day parts.
6. In the dialog box, enter 4 for the number of characters.
7. For the Split option, select Once, as Far Right as Possible.
8. Click OK to close the dialog box.

The Became a State column is split into two columns. Delete the column that shows the month and day. Rename the year column to Year.

Notice that the query is named Table 0 in the Query Setting task pane. Change the name to State Join Year.

Finally, choose Home Close Close & Load. The data is transferred to a new worksheet (see Figure 38.16).

At this point, we have an Excel table that includes the state names and statehood year. Next, we need to perform another query to get the population data.

Performing the second web query

Follow these steps to perform the second web query (using the same workbook as the first query).

1. Activate any empty cell and choose Data Get & Transform New Query From Other Sources From Web. Excel displays a dialog box where you can enter the URL that contains the data.
2. Enter the following URL and click OK:

   http://simple.wikipedia.org/wiki/List_of_U.S._states_by_population

   Excel displays a Navigator dialog box that lists all data sources found at the URL.

3. Select Table 0 and click Edit. The Query Editor appears.
4. Remove all the columns except State and Population.
5. Choose Home Transform Use First Row As Headers.
6. In the Query Settings task pane, enter a name to make it easier to identify the query. I used State Population as the name.
7. Choose Home Close Close & Load. The data is transferred to a worksheet (see Figure 38.17).

At this point, we have two queries and two results tables. Because the two tables have a common column (State), it's easy to transform them into a single table by merging the two queries.

Merging the two queries

Follow these steps to merge the two queries and create a single table:

1. Activate any cell in the first table (State Join Year) and choose Data Get & Transform New Query Combine Queries Merge. Excel displays the Merge dialog box where you can specify the queries to merge.
2. In the upper part of the Merge dialog box, specify State Join Year as the table and highlight the State Name column in the preview.
3. In the lower part of the Merge dialog box, specify State Population as the table and highlight the State column header in the preview (see Figure 38.18).

   

4. Click OK to close the Merge dialog box.

   Excel displays the Query Editor, with a new column (named NewColumn) added to the original State Join Date query. The new column displays “Table” for each row. The column needs to be expanded to show the actual data.

Follow these steps to expand the field:

1. Select the NewColumn column header and choose Transform Structured Column Expand. Excel displays the Expand New Column dialog box.
2. In the Expand New Column dialog box, select only the Population item.
3. Click OK to close the dialog box. The column displays the population.
4. Rename the new column Population.
5. Select the Population column and choose Home Transform Data Type Whole Number.
6. In the Query Settings task pane, enter a name to make it easier to identify the query. I used Merged Queries as the name.
7. Click Home Close Close and Load. Excel inserts a new worksheet with another table, which is the first two tables combined.

Now the workbook contains three tables, and each was generated by a query. Figure 38.19 shows the combined table (after applying number formatting) plus the Workbook Queries task pane.

Keep in mind that the query definitions are stored with the workbook and are listed in the Workbook Queries task pane. In this example, you can delete the first two tables (but keep the queries). The third query (Merged Queries) does not depend on the table results of the first two queries.

Example: Getting a List of Files

If you've ever needed to import a list of files into an Excel workbook, you probably realized that there is no direct way to do so. Using Get & Transform, however, makes this task easy. And you may be amazed how fast it is.

I have a digital music library that contains more than 67,000 MP3 files, organized in folders and subfolders, like this:

Genre

Artist

Album

A few years ago I wrote a VBA macro to read these files and put the information in a worksheet. The macro took more than an hour to run. Using Get & Transform, I can generate that list of files in a matter of seconds.

I started by using the Data New Query From File From Folder command and specified the directory that contains all the music files. Figure 38.20 shows the Query Editor. I removed the following columns: Content, Date Accessed, Date Modified, and Date Created.

Next, I selected the Extension column and applied a filter to select only files with an .mp3 extension. After applying the filter, I removed that column.

I then expanded the Attributes column by using Transform Structured Column Expand. The only attribute I needed was Size.

Next, I needed to extract the Genre, Artist, and Album Name from the Folder Path column. An example of a Folder Path entry is

E:MUSICJazzMiles DavisKind Of Blue

I selected the Folder Path column and chose Home Text Column Split Column By Delimiter. I used a backslash character as the delimiter and specified At Each Occurrence of the Delimiter. This resulted in five columns. The first two extracted columns (E: and MUSIC) are not needed, so I deleted them. I renamed the other three extracted columns to be Genre, Artist, and Album.

The Size column lists the file size in bytes, and I would prefer megabytes. I selected the column, chose Transform Number Column Standard Integer-Divide, and specified 1024 as the divisor.

Satisfied with the query, I used Home Close Close & Load to import the result into the worksheet. Finally, I created a pivot table to summarize the data into an attractive report. I added two slicers (Genre and Artist) to make it easy to display specific music types (see Figure 38.21).

Example: Choosing a Random Sample

The example in this section demonstrates how to generate a random sample from a data source. The data is the English Open Word List (EOWL), which contains more than 128,000 English words with ten or fewer letters. These words are contained in a CSV file. The goal is to generate a list of 20 random words from this file.

Start with a new workbook and choose Data Get & Transform New Query From File From CSV. In the Import Data dialog box, locate the word list.csv file and click Import. The Query Editor opens, with a single column of words, in alphabetical order. Rename this column to Words.

One way to choose random items is to assign a random number to each item, sort using the random numbers, and then work with the top “n” sorted items (in the case, the top 20).

The Query Editor doesn't have a command to insert a column of random numbers, but I did some research and discovered a function that will do the job. To add a column of random numbers to the Word List query, perform the following steps in the Query Editor:

1. Choose Add Column General Add Custom Column. Excel displays the Add Custom Column dialog box.
2. Name the column Random, and enter this formula (see Figure 38.22):

   =List.Random(1)

   

3. Click OK. Excel adds a new column, but the random numbers aren't displayed. Rather, each item displays as “List.”
4. Click the small icon to the right of the column header. The random numbers appear in the column.

Next, click the down arrow next to the column header and choose Sort Ascending. The rows are sorted by the Random column.

To limit the query results to the first 20 items, choose Home Reduce Rows Keep Rows Keep Top Rows, and enter 20 for the number of rows to keep. You can remove the Random column because it's no longer needed.

To make it slightly more interesting, add a new column that displays the number of characters in each word. Select the Words column and choose Add Column General Duplicate Column. Rename the new column Characters. Then select the Characters column and choose Transform Text Column Length. Excel transforms this column of words to the number of characters in each word.

It might be preferable to display the words in uppercase. Select the Words column and choose Transform Text Column Format UPPERCASE. Finally, click the down arrow to the right of the Words column name and choose Sort Ascending. This will display the random words in alphabetical order.

Choose Home Close Close & Return, and 20 random words are returned to the worksheet (see Figure 38.23). When you refresh the query, 20 new words are displayed. On my system, refreshing the query is practically instantaneous.

Example: Unpivoting a Table

In Chapter 34, I describe a “reverse pivot table” VBA macro that converts a two-way summary table into a three-column normalized list. Get & Transform provides another way to perform this type of data reorganization.

Figure 38.24 shows the original data: monthly values for each of the 50 states. The goal is to convert this into a three-column table with 600 rows (one row for each data point).

Follow these steps:

1. Select any cell in the table and choose Data Get & Transform From Table. Excel displays the Query Editor.
2. Select all columns except State, and choose Transform Any Column Unpivot Columns. Query Editor displays the data, converted to three columns.
3. Click Home Close Close & Load. The query results are loaded to a new worksheet (see Figure 38.25).

Tips for Using Get & Transform

Following are some points to keep in mind when using Get & Transform:

• When working in the Query Editor, make sure the data type is correct. For example, values in columns may look like dates, but they could be text. To check the data type, select the column and look at the Data Type drop-down in the Home Transform group.
• If you insert a new column that uses other columns in a calculation, you can (if you like) delete the columns used by the new column.
• The Workbook Queries task pane lists all queries stored in the workbook. Toggle the display of this task pane using Data Get & Transform Show Queries.
• When you hover your mouse over an item listed in the Workbook Queries task pane, you see a preview of the query results, plus additional information about the query. For example, you can see the data source and when the query was refreshed.
• The Applied Steps lists in the Query Settings task pane can serve as a type of “audit trail” to document the changes made when importing your data. You can click an earlier step to see how the data looked before a particular step was applied.
• It's a good idea to provide a meaningful name to each query, especially if your workbook contains multiple queries. Name your query in the Properties section of the Query Settings task pane.
• You can easily remove columns in the Query Editor, but options for removing rows are limited. You can remove rows only from the top or the bottom of the data. This allows you to ignore extraneous information such as table titles and footnotes. You can also remove alternate rows, which is a way to get a smaller sample of data.
• Data used by Get & Transform doesn't have to be from an external source. Use the Data Get & Transform From Table command to work with a table of data in your workbook.
• Use the Data Connections Connections command to see a list of all the data connections in your workbook. Click the Properties button in the Workbook Connections dialog box to view (and set) various options for a data connection. For example, you can make the query refresh automatically at a specified time interval.
• If Get & Transform doesn't seem capable of performing a transformation you need, take some time and learn about functions and formulas. Use Add Column General Add Custom Column, and then click the Learn About Get & Transform Formulas link in the dialog box.
• If you use the Advanced Editor in the Query Editor, be aware that the functions are case sensitive.

Learning More

The examples in this chapter demonstrate some basic (and relatively simple) uses for the Get & Transform feature. Complete books are available that cover more advanced features. And, of course, you'll find lots of useful information on the web.