Power query table.combine dynamic

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.

Inhaltsverzeichnis Show

Watch a Video ?
The Problem – Expand All Columns
Dynamically Expand All Columns with Existing Columns
More on Power Query
Case: When the source data is in a different workbook than the output table
Sample data tables
Step 1: Get Data
Step 2: Delete all steps except Source
Dynamic Output

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!

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..

Use the “fx” to create a new step
Use the Table.Combine function to combine tables from the Data column in the previous step
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)

Partners filmexxx.link, sextotal.net, ahmedxnxx.com, fucktube, russianxnxx

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

Welcome to Goodly! My name is Chandeep. On this blog I actively share my learning on practical use of Excel and Power BI. There is a ton of stuff that I have written in the last few years. I am sure you'll like browsing around. Please drop me a comment, in case you are interested in my training / consulting services. Thanks for being around Chandeep

One of the key tasks as an analyst is to combine and transform data, before we start analyzing it. Power Query is very handy in such situations.