Expand All Columns Dynamically in Power Query

This is one those problems that occurs when you have worked quite a bit with Power Query user interface but there seems to be no workaround to expand all columns dynamically.

 

Watch a Video ?

The Problem – Expand All Columns

While expanding (combining) data from multiple sources the column names get hardcoded.

For eg. See below Table.ExpandTableColumn function has hard coded column names (“Date”, “Sales Rep”,…), that means any new columns added in the source data won’t change the hardcoded columns.

Bummer!

 

Expand All Columns Dynamically in Power Query

To be able to get this going we need a slightly different approach with a slight dose of M. Below, I have a half cooked query where a few tables need to be combined from the Data Column.

Caution – Don’t expand the Data Column.

 

Instead do this..

1. Use the “fx” to create a new step
2. Use the Table.Combine function to combine tables from the Data column in the previous step
3. Done

Here is the code

= Table.Combine ( #'Renamed Columns'[Data] )

The trick is to combine data and not expand it 😎 Table.Combine will take care of any new columns being added to the source.

 

Dynamically Expand All Columns with Existing Columns

One tweak in this problem could be to dynamically expand all columns along with a few columns from the existing step.

Consider this table where I need all columns for all tables in the Data Column along with the Name Column (which contains the sheet name)

 

Step 1 – I create a new step named as ColNames with the following code

= Table.ColumnNames ( Table.Combine ( #'Renamed Columns'[Data] ) )

Step 2 – Once again, create a new step named “Expanded Data” with the following formula.

= Table.ExpandTableColumn ( #'Renamed Columns', 'Data', ColNames )

Notice, along with all other columns we also get the Name Column from the previous step. The trick is to use Table.ExpandTableColumn but with a dynamic list of columns that we generated in the previous step. 😎

 

 

More on Power Query

1. Combine Data from Multiple Excel Files – Video
2. Combine Data from Multiple Excel Sheets
3. Create Dynamic File Path in Power Query
4. Unstack Rows in Separate Columns
5. Change Dates from mm-dd to dd-mm format (or vice versa)
6. Split by Variable Columns in Power Query
7. Replace Errors in Multiple Columns