Dynamically Expanding All Columns with Power Query

When working with Power Query, you might sometimes need to expand Table or Record columns in order to get to the data you want. For example, a table named “A” may be comprised of three normal columns and a fourth column containing a record. This means that the fourth column is made up of MULTIPLE columns that you can drill into. The way you drill down to the next level is very intuitive within Power Query, but the column names will be hard-coded (FOREVER). If your data source changes or the available columns vary for each record, you may face problems as you will only expand certain columns because of the hard-coding.

In short, the solution is to expand the record or table dynamically by understanding all the possible child columns and then expanding the parent record/table with said child columns. The video above provides a more in-depth approach as to how I arrived at the following piece of code, but I will outline each step below:

Solution:

Table.ExpandRecordColumn(

   MyTable,

   “ColumnToExpand”,

   Table.ColumnNames(

      Table.FromRecords(

         List.Select(

            Table.Column(

               MyTable,

               “ColumnToExpand”,

             ) each _ <> “” and _ <> null
         )

      )

   ),

   Table.ColumnNames(

      Table.FromRecords(

          List.Select(

                Table.Column(

                    MyTable,

                    “ColumnToExpand”,

                  ) each _ <> “” and _ <> null
             )

        )

   ),

)

Explanation

This code may look a bit daunting, but it is easy to understand if you break it down in pieces. I’ve divided the code into three colored sections, blue, green, and orange. We’ll start with the green section beginning from the inside and working out. The List.Select(Table.Column()) piece is simply turning our expandable column into a list. We then transform this list into a table with Table.FromRecords() and then we grab the column names with the Table.ColumnNames() function. This provides us with all of the possible columns in our “ColumnToExpand” column. The blue section is using the Table.ExpandRecordColumn() function to expand our “ColumnToExpand” column, but since we are using the green section as input, we are dynamically expanding all of the columns available to us. The orange section is a repeat of the green section and is used as the 4^th parameter to the blue section, providing the names of the expandable columns.

And with that, we are now able to expand a table or record column dynamically based on the available column names in the child table/record. We no longer have hard-coded column names in our code!