Data Explorer: An introduction to table relationships and the Expand operation

This post is an introduction to table relationship concepts as related to the Data Explorer Expand operation. The Expand operation makes combining data together really easy.

One of the goals of efficient data management design is to remove duplicate data that is contained in a data source. To achieve that goal, a data management application is divided into many subject tables so that each fact is represented only once. For example, an invoice design might have an Order table and related Order_Details table, in which one order has many order details. To bring the divided information back together, the columns in the tables must be coordinated so that they show information about the same order. This coordination is accomplished by using table relationships. A table relationship works by matching data in key columns — often a column with the same name in both tables. In most cases, these matching columns are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. An order can be associated with order line item details by creating a table relationship between an OrderID column in the Order table and Order_Details table. In this example, the Order table is the primary table and the Order_Details table is the related table.

In Microsoft “Data Explorer” Preview for Excel, you create a query to bring data source tables back together. In the Order example, you can use the Expand query operation to bring together Order_Details records that are related to the Order table, to combine order line items with each order. The Expand operation widens a subject table to include columns from a related table and expands a subject table with values from the related table. To illustrate:

A subject table has columns A and B.

A related table has column C.

The Expand operation widens a subject table to include column C and expands a subject table with related values from the data source that contains column C.

In the Order example, the Expand operation widens an Order table to include the Order_Details.ProductID, Order_Details.UnitPrice, and Order_Details.Quantity columns to bring together Order records and related Order_Details records. For more information about the Expand operation, see Expand a column containing an associated table and the Combine data from multiple data sources tutorial.

For more information about the Microsoft “Data Explorer” Preview for Excel, see https://www.microsoft.com/en-us/bi/Products/Office.aspx.