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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/179/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I expand the icon next to Tent Packs.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/179/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I select the two columns I want, and then continue.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/179/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/179/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
However, when I look at my merged query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/179/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
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"))
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/179/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
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"}))
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/179/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
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!