CODEWITHSUNDEEP
powerquerym
Marc Pincince
Marc Pincince

Reputation: 5202

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?

Upvotes: 0

Views: 1666

Answers (2)

KiwiPete
KiwiPete

Reputation: 1

Try using Table.Join which joins and expands the second table in one step.

"Merged Queries" = Table.Join(Source,{"Index.1"},Table2,{"Index.2"},JoinKind.LeftOuter)

You just need to make sure that the columns between the tables are unique. Use Table.PrefixColumns to ensure column names are unique

Upvotes: 0

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

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.

Upvotes: 1

Related Questions