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!
