Search code examples
powerquerym

Is there a way to dynamically identify and expand an embedded table's columns?

If I want to expand this embedded table...

enter image description here

...and I click on the expand button, I'm presented with the dropdown to select which columns I want to expand:

enter image description here

However, if I choose '(Select All Columns)' to expand them all, Power Query turns that into hard-coded column names of all the columns at the time I do that. Like this:

= Table.ExpandTableColumn(Source, "AllData", {"Column1", "Column2", "Column3", "Column4", "Custom"}, {"Column1", "Column2", "Column3", "Column4", "Custom"})

After that, if the underlying embedded table's columns change, the hard-coded column names will no longer be relevant and the query will "break."

So how can I tell it to dynamically identify and extract all of the current columns of the embedded table?

Solution

  • You can do something like this to get the list of column names:

    List.Accumulate(Source[AllData], {}, (state, current) => List.Union({state, Table.ColumnNames(current)}))

    This goes through each cell in the column, gets the column names from the table in that cell, and adds the new names to the result. It's easier to store this in a new step and then reference that in your next step.

    Keep in mind that this method can be much slower than passing in the list of names you know about because it has to scan through the entire table to get the column names. You may also have problems if you use this for the third parameter in Table.ExpandTableColumn because it could use a column name that already exists.