Work with a List, Record, or Table structured column (Power Query)

There are three types of structured columns:

• List    Stores a list of values, For example, { 1, 2, 5, 10 } or { "hello", "hi", "good bye" }. A value of List in a cell is the simplest structured column and there is no table relationship.

• Record    Stores a set of named fields grouped into a unit. For example, [ FirstName = "Joe", LastName = "Smith", Birthdate = #date(2010, 1, 2) ]. A value of Record  in the cell indicates a one-to-one table relationship with the current or primary table.

• Table    Stores a table that has a secondary relationship with the current data, which is the primary table. A value of Table in the cell indicates a one-to-many relationship with the current or primary table.

A Record and Table structured column represents a relationship in the data source which has a relational model, such as an entity with a foreign key association in an OData feed or a foreign key relationship in a SQL Server database.

You can use the Expand  icon to see columns from a related table. For example, in an Orders table, an expand operation brings together Order_Details records that are related to the Order table to combine order line items with each order. The expand operation widens a primary table to include columns from a secondary and related table. To illustrate:

A primary table has columns A and B.

A related table has column C.

The expand operation widens a primary table to include column C and expands a primary table with related values from the secondary and related table that contains column C.

To summarize, when you expand a Table structured column, the values are displayed alongside the other columns in Data Preview. For more information about combining primary and related tables, see Learn to combine multiple data sources. 

In this 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 primary table Order rows and related table Order_Details rows.

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.
   
   Note    The query must have at least two tables joined in a relationship. For more information, see Merge queries. 

1. Click the expand icon () in the column header of the structured column.

2. In the Column Names drop-down list, select the columns you want and clear the columns you don't want. 
   
   

3. Select OK.

Result

The table now contains a new column for each of the columns selected in step 3. 

When you expand a Record structured column, the values are displayed as new columns in Data Preview. If you don't expand it and then load a query to a worksheet, you see a placeholder value of [Record] for each cell in the column.

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

2. To see a specific value without expanding the column, select a value in the structured column that has a Record value. For example:
   
   

3. Select the expand icon () in the column header of the structured column.

4. In the Column Names drop-down list, select the columns you want and clear the columns you don't want.
   
   

5. Select OK.

Result

The table now contains a new column for each of the columns selected in step 4. 

When you expand a List structured column, the values are displayed as new columns in Data Preview. If you don't expand it and then load a query to a worksheet, you see a placeholder value of [List] for each cell in the column.

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

2. To see a specific value without expanding the column, select a value in the structured column that has a List value. For example:
   
   

3. Select the expand icon () in the column header of the structured column.

4. Do one of the following:

   • To create a row for each list value, select Expand to New Rows.

   • To create a list of text values separated by a delimiter, select Extract Values.

5. Select OK.

Result for  Expand to New Rows

Result for  Extract Values with a semicolon (;) as a delimiter

You can use the following additional commands on the Power Query Editor ribbon to work with structured columns:

1. Select Home > Transform and then select one the following commands in the Structured Column group.

   Expand    Promotes a structured column (List, Records, or Table) to become new columns and rows in the current query table. This command is the same as the Expand icon.
   
   Aggregate    Summarize values in a Table structured column by using aggregate functions, including Sum and Count. This command is the same as selecting the Expand  icon, and then selecting Aggregate in the Column Names drop-down dialog box. It is only available for the Table structured column.
   
   Extract Values Extracts the values of a structured List column by combining them into a single text value using a specified delimiter.

Note    You can also use the Statistics command in the Number Column group (Select Transform > Statistics) with a Table structured column. It has the same list of aggregate functions as the Statistics command in the Numeric List group of the Transform context tab under List Tools in the Power Query Editor ribbon.

You can use the Drill Down command to display the Transform context tab under List Tools in the Power Query Editor ribbon and reveal additional commands to work with structured columns. Do not confuse this Transform tab with the other Transform tab that is regularly available with the Power Query Editor ribbon.

1. Right click the column header and then select Drill Down.

2. To convert the List or Record to a Table, under List Tools, select Transform > To Table.

3. To keep top, bottom, or a range of items, under List Tools, select Transform > Keep Items.

4. To remove top, bottom, or alternate items, under List Tools, select Transform > Remove Items.

5. To remove duplicate values, under List Tools, select Transform > Remove Duplicates. For more information, see Keep or remove duplicate rows.

6. To reverse items, under List Tools, select Transform > Reverse Items.

7. To sort the data, under List Tools, select Sort Ascending  or Sort Descending . For more information, see Sort data.

8. To display various statistics, under List Tools select Transform > Statistics. The following statistics are available: Sum, Minimum, Maximum, Median, Average, Standard, Deviation, Count Values, and Count Distinct Values. For more information, see Aggregate data from a column.