Let’s say we have an Excel file with several smart tables:
If you load these tables into Power Query in the standard way using the command Data – Get data – From file – From book (Data — Get Data — From file — From Workbook), then we get something like this:
The picture, I think, is familiar to many Power Query users. Similar nested tables can be seen after combining queries (a la VLOOKUP), grouping (command Group by tab Transformation), importing all files from a given folder, etc.
The next logical step in this situation is usually to expand all nested tables at once – using the button with double arrows in the column header Data:
As a result, we get an assembly of all rows from all tables into a single whole. Everything is good, simple and clear.
Now imagine that a new column (Discount) was added in the source tables and/or one of the existing ones (City) was deleted:
Then our request after the update will return a not so beautiful picture – the discount did not appear, and the city column became empty, but did not disappear:
And it’s easy to see why – in the formula bar you can clearly see that the names of the expanded columns are hardcoded in the function arguments Table.ExpandTableColumn as lists in curly brackets.
Getting around this problem is easy. First, let’s get the column names from the header of any (for example, the first) table using the function Table.ColumnNames. It will look like:
Here:
- #”Other columns removed” – the name of the previous step, where we take the data from
- 0 {} – the number of the table from which we extract the header (counting from zero, i.e. 0 is the first table)
- [Data] – the name of the column in the previous step, where the expanded tables are located
It remains to substitute the construction obtained in the formula bar into the function Table.ExpandTableColumn at the step of expanding tables instead of hard-coded lists. It should all look like this in the end:
That’s all. And there will be no more problems with expanding nested tables when the source data changes.
- Building multiformat tables from one sheet in Power Query
- Build tables with different headers from multiple Excel files
- Collecting data from all sheets of the book into one table