有没有办法动态识别和扩展嵌入式表的列？(Is there a way to dynamically identify and expand an embedded table's colum

有没有办法动态识别和扩展嵌入式表的列？(Is there a way to dynamically identify and expand an embedded table's columns?)

如果我想扩展这个嵌入式表...

...然后我点击展开按钮，我会看到下拉列表以选择要扩展的列：

但是，如果我选择“（选择所有列）”将它们全部展开，Power Query会在我执行此操作时将其转换为所有列的硬编码列名称。 喜欢这个：

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

之后，如果底层嵌入表的列发生更改，则硬编码列名称将不再相关，查询将“中断”。

那么我怎么能告诉它动态识别和提取嵌入式表的所有当前列？

If I want to expand this embedded table...

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

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?

最满意答案

您可以执行以下操作来获取列名称列表：

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

这将遍历列中的每个单元格，从该单元格中的表中获取列名称，并将新名称添加到结果中。 将其存储在新步骤中更容易，然后在下一步中引用它。

请记住，此方法比传入您知道的名称列表要慢得多，因为它必须扫描整个表以获取列名称。 如果将此用于Table.ExpandTableColumn的第三个参数，则可能会出现问题，因为它可能使用已存在的列名。

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.