Power Query: Structured Columns Deconstructed

In Aggregating Aggravating Worksheets, I looked at how to aggregate three worksheets into one query. I’ll run through the process again briefly, as my main aim today is to explore the ‘Structured Column’ section on the ‘Transform’ tab, which is not available for most column types.

My workbook, containing the three worksheets, is shown below:

I extracted the data from these worksheets by creating a new ‘Blank Query’ from the ‘POWER QUERY’ tab in the ‘From Other Sources’ dropdown:

In the Power Query Editor, in the formula bar at the top of the screen, I enter the following formula:

 This lists my tables (and would list named ranges and workbook connections) that I have in my workbook.

It is at this point that I deviate from aggregating worksheets to investigate the section on the ‘Transform’ tab which is normally greyed out, the ‘Structured Column’ section.

‘Expand’ looks very similar to the icon next to my Content column. To see if this is true, I try using each option, firstly the icon next to Content:

I can choose to ‘Expand’ or ‘Aggregate’ my data, and decide what columns I want to bring through. I try using the ‘Expand’ option from the ‘Structured Column’ section.

A different size and shape perhaps, but no aggregate option here. I would need to pick the ‘Aggregate’ option from the ‘Structured Column’ section.

Therefore, this is a quicker way to get to the aggregate options.

Extract values is still greyed out as an option on the structured column, so to find out what this does I will create a column that contains a list.

The M code for a list is simple:

which creates a short list of three lovely names. I click 'OK' to create my column:

Not only has my List column appeared, but ‘Extract Values’ is no longer greyed out. So I try it.

The icon next to List can also be used, and this gives access to both the extract values and expand options:

If I continue to extract my values, I can pick a delimiter to separate my list items:

Clicking 'OK' means that my list has been extracted into my table. Finally, I can expand all my data and tidy it up.

The functions in the ‘Structured Column’ section can be also be accessed from the icons, but sometimes seeing the words can make it easier to see what is needed and jump right to what I need to do.

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!