When I’m working with XML files, or web pages, or any data with columns containing nested tables in Power Query, I often end up having to expand every expandable column in the table and then expanding any new columns that are revealed after that to find the data that I’m looking for. This is a such a pain I thought I’d write a function to do it for me – which is the subject of this post.
For example, consider the following XML:
If you load this into Power Query you will see the following table created for the first step:
To get to a table where all of the data is visible requires clicking on the expand icons in the address and the employees columns (highlighted), and then three more clicks after that. Sigh.
Here’s my function, called ExpandAll, to expand all the columns in a table that can be expanded:
You can then use this function on the XML file shown above as follows:
And bingo, in one step, you get everything:
You can download the sample workbook here.