Please note javascript is required for full website functionality.


Power Query: Dynamic Merging

6 May 2020

Welcome to our Power Query blog. This week, I look at working dynamically with merged queries.

I have some data from my imaginary salespeople, which I have loaded into Power Query and merged.

I expand the icon next to Tent Packs.

I select the two columns I want, and then continue.

The problem is, the M code created for this step is:

= Table.ExpandTableColumn(Source, “Tent Packs”, {“Item”, “Cost”}, {“Item”, “Cost”})

which specifically mentions the column names Item and Cost, meaning that if I add more columns to Tent Packs they will not be expanded.  I can add a new column, Supplier, to show this.

However, when I look at my merged query:

Since the expand step specifies column names, the new column is not included.  I need another way to create this step so that it can be dynamic.  My current step is:

= Table.ExpandTableColumn(Source, "Tent Packs", {"Item", "Cost"}, {"Item", "Cost"})

The last two parameters are lists of column names: {"Item", "Cost"} and {"Item", "Cost"}.  Instead of this, I can get the column names from the table Tent Packs:

=Table.ExpandTableColumn(Source, "Tent Packs", Table.ColumnNames(#"Tent Packs"))

This time I have all three of my columns.  In my case, the linked column in Tent Sales has a different name to its corresponding column in Tent Packs, i.e. Tent Pack links to Pack Number.  If they had the same name, then I can avoid extracting Pack Number by removing it from the list of columns.

=Table.ExpandTableColumn(Source, "Tent Packs", Table.ColumnNames(#"Tent Packs"))

This would change to:

= Table.ExpandTableColumn(Source, "Tent Packs", List.RemoveItems(Table.ColumnNames(#"Tent Packs"), {"Pack Number"}))

I can then add steps to this query knowing that all columns from Tent Packs will be expanded.

Come back next time for more ways to use Power Query!