Dynamically expanding columns

Overview

In this article we explain how to expand columns of data from multiple sheets – dynamically, so that the transformation will still work if new columns are added, or if not all the columns across the sheets have the same heading.

Shown below are 3 sheets of data contained in the same one Excel file. As you can see, column names are not the same across all the sheets – but we would like to import all the data.

Steps

These are the steps to follow:

Import the Data into PQ

When you import the file into Power Query, it should look something like this:

Click to view large-sized image

Promote headers

If you click on a cell in the Data column, you should see the data from that sheet, like shown below:

Click to view large-sized image

The data, however, does not have the first row promoted to headers.

  • Adding the following step will promote the headers in each sheet, and store the results in a table object in a column called Custom – like shown below:

Click to view large-sized image

Use Table functions

  • Using the Table.Combine and Table.ColumnNames functions will vertically list all the headers across all the sheets
  • Then, using the Table.SelectColumns function will select the ‘Custom’ and ‘Name’ columns from the earlier ‘#AddedCustom’ step
  • Finally, the Table.ExpandTableColumn function will expand all the columns defined in the earlier ‘ColNames’ step

Click to view large-sized image

That’s it! Up to this point, the query should still work - even if new columns are added (to any of the sheets), and also if there is a name change to any of the existing column headings. (You need to make sure though that after this point in the query, you don’t add a Change datatype step, as this will hardcode the column headings into the actual query - which you don’t want. Instead, the data formats can be set later in Excel)

Load Data to Excel

When you load the data back to Excel, based on the original sheet data shown above, the output should look something like this:

Final M-Code

Here is the final code used for the query - (though the file path to to where the source Excel data file is maybe different to yours):

M-Code 
let
    Source = Excel.Workbook(File.Contents("C:\Users\OneDrive\Desktop\Dynamically Expanding Columns - Data.xlsx"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders([Data])),
    ColNames = Table.ColumnNames(Table.Combine(#"Added Custom"[Custom])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom", "Name"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", ColNames)
in
    #"Expanded Custom"