Combine multiple queries

[This documentation is for preview only, and is subject to change in later releases.]

Microsoft “Data Explorer” Preview for Excel provides an intuitive user interface for combining multiple queries within your Excel workbook by merging or appending them. The Merge and Append operations are performed on any Data Explorer query with a tabular shape that is independent of the data source that the data comes from. For procedural steps describing how to combine multiple queries, see the Combine data from multiple data sources tutorial.

I want to

Merge queries

The Merge operation creates a new query from two existing queries. One of these query results contains all columns from a primary table, with one column serving as and a single column containing a navigation link to a related table. The related table contains all rows that match each row from a primary table based on a common column value. An Expand operation adds columns from a related table into a primary table. For an example of merging total sales from an order details query into a products table, see the Combine data from multiple data sources tutorial.

Security   Privacy Levels prevent a user from inadvertently combining data from multiple data sources, which may be private or organizational. Depending on the query, a user could inadvertently send data from the private data source to another data source that might be malicious. Data Explorer analyzes each data source and classifies it into the defined level of privacy: Public, Organizational, and Private. For more information about Privacy Levels, see Privacy Levels.

Perform a Merge operation

  1. In the DATA EXPLORER or QUERY ribbon tab, in the Combine group, click Merge. When using the QUERY ribbon tab, the active query is selected as the primary table for the Merge operation.
  2. In the Merge dialog box, select the primary table and related table to merge.
  3. In the primary table and related table preview, select the same number of columns to match.
  4. Click OK.

 Note    The common columns in the primary table and the related table are compared based on the order selected for each table. In addition, columns must be the same type, such as Text or Number, so that they match.

After you select columns from a primary table and related table, Data Explorer displays the number of matches out of the top rows. This action validates whether the Merge operation was correct or whether you need to make changes to Merge settings or to the queries that you want to merge.

The following Merge example illustrates a Merge operation between a Products primary table and a Total Sales related table.

Merge into a query

After you click OK, the Merge operation creates a new query.

Merge Final

Perform an Expand operation

After a Merge operation, you can expand a Table link column to add columns from the related table into a primary table. Once a column is expanded into the primary table, you can apply filters and other transform operations.

Expand a column

  1. In the Preview pane, click the NewColumn expand icon (Expand ).
  2. In the Expand drop-down:
  1. Click (Select All Columns) to clear all columns.
  2. Click the columns from the related table to add to the primary table.
  3. Click OK.

 Note    After you expand a column, you can rename the column. For more information about how to rename a column, see Rename a column.

Top of Page Top of Page

Append queries

The Append operation creates a new query that contains all rows from a first query followed by all rows from a second query.

Security   Privacy Levels prevent a user from inadvertently combining data from multiple data sources, which may be private or organizational. Depending on the query, a user could inadvertently send data from the private data source to another data source that might be malicious. Data Explorer analyzes each data source and classifies it into the defined level of privacy: Public, Organizational, and Private. For more information about Privacy Levels, see Privacy Levels.

Perform an Append operation

  1. In the DATA EXPLORER or QUERY ribbon tab, in the Combine group, click Append. When using the QUERY ribbon tab, the active query is selected as the primary table for the Append operation.
  2. In the Append dialog box, select the first and second tables (queries) to append.
  3. Click OK.

Once the Append operation completes, Data Explorer displays a new query in the query editor.

Top of Page Top of Page

 
 
Applies to:
Excel 2013