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.

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

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)

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.

Case: When the source data is in a different workbook than the output table

The underlying assumption is that all sheets have the same data table structure.

For source table same as the output table, refer to the article:

Sample data tables

Data is on four different sheets.

Download Sample Data

Step 1: Get Data

Data > Get Data > From File > From Workbook

Select one sheet > Transform Data

Step 2: Delete all steps except Source

Home > Use the First Row as Header

Filter out the remaining header rows.

Right-click > Filters > Does Not Equal

Result

In case we need to include sheet name in the final output, make the following adjustment in Step 3.

Select Name + Data column > Right Click > Remove Other Columns

Dynamic Output

Load the query output to a pivot table.

Home > Close & Load To > Pivot Table

Any change in the workbook, Power Query updates the output accordingly.