0

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?

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you @Alejandro Lopez-Lago! This does the trick. Would you mind explaining what the syntax actually means/does as written? I'd like to really understand it. For instance, I don't know what state and current do/mean/provide.
List.Accumulate takes a list of values and reduces it down to one value by using an accumulator. It takes three parameters: the list of values, the initial value of the accumulator, and a function that passes in the current value of the accumulator (state) and the current value in the list (current) which then returns the new value of the accumulator. In this case we use List.Union to take both lists, combine them, and remove duplicate values.
Also, if the columns are the same in each cell's table, you can just use Table.ColumnNames(Source[AllData]{0}). {0} gets the value of the first element.
Thanks again @Alejandro Lopez-Lago. ...Especially for the follow-up explanation.
I'm having the same exact question except I have dozens of rows, each with varying amounts of columns in their "Table". The provided solution works, but... takes forever. Is there a way to find the "Table" with the maximum columns and leverage that instead of the first element? Like: MaxCols = List.Max(Row with the mostest) then Table.ColumnNames(Source[AllData]{MaxCols }). {MaxCols }
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.