问题

我正在尝试从文件夹导入一个xlsx文件。

我已经有一个有效的解决方案：

   FilePath = Excel.CurrentWorkbook(){[Name="fp"]}[Content]{0}[TheFilePath],
   Source = Excel.Workbook(File.Contents(FilePath)),
   # "Kept First Rows" = Table.FirstN(Source,1),
   # "Expanded Data" = Table.ExpandTableColumn(# "Kept First Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11"}),
   # "Removed Columns" = Table.RemoveColumns(# "Expanded Data",{"Name", "Item", "Kind", "Hidden"}),
   # "Promoted Headers" = Table.PromoteHeaders(# "Removed Columns", [PromoteAllScalars=true]),
   # "Changed Type" = Table.TransformColumnTypes(# "Promoted Headers",List.Transform(Table.ColumnNames(# "Promoted Headers"),each {_,type text}))
in
    # "Changed Type"

但是文件是动态的，它会不断被替换，列数也会发生变化。

所以我正在尝试用这个替换：

# "Expanded Data" = Table.ExpandTableColumn(# "Kept First Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11"})

使用这个：

# "Expanded Data" = Table.ExpandTableColumn(Source, "Data", {Table.ColumnNames(Source)})

我得到了错误：

详细信息:
    值=[List]
    类型=[Type]```

Source的图片

[![在这里输入图片描述][1]][1]

如何解决这个错误？

  [1]: https://i.stack.imgur.com/dTzyX.png

<details>
<summary>英文:</summary>

I am trying to import an xlsx file from a folder.

I have, which works

let
FilePath = Excel.CurrentWorkbook(){[Name="fp"]}[Content]{0}[TheFilePath],
Source = Excel.Workbook(File.Contents(FilePath)),
#"Kept First Rows" = Table.FirstN(Source,1),
#"Expanded Data" = Table.ExpandTableColumn(#"Kept First Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Name", "Item", "Kind", "Hidden"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",List.Transform(Table.ColumnNames(#"Promoted Headers"),each {_,type text}))
in
#"Changed Type"

But the file is dynamic in that it keeps getting replaced, and the number of columns keeps changing.

So I am trying to replace this.

#"Expanded Data" = Table.ExpandTableColumn(#"Kept First Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11"})

with this

#"Expanded Data" = Table.ExpandTableColumn(Source,"Data",{Table.ColumnNames(Source)})

I get the error

Expression.Error: We cannot convert a value of type List to type Text.
Details:
Value=[List]
Type=[Type]

Image of Source

[![enter image description here][1]][1]

How to resolve this error?


  [1]: https://i.stack.imgur.com/dTzyX.png

</details>


# 答案1
**得分**: 1

如果您正在尝试扩展"Data"列，可以使用以下代码动态工作在列名上：

```plaintext
List = List.Union(List.Transform(#"Kept First Rows"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Kept First Rows", "Data", List, List)

或者在"Data"前预先添加Data.：

List = List.Union(List.Transform(#"Kept First Rows"[Data], each Table.ColumnNames(_))),
List2 = List.Transform(List, each "Data."&_),
#"Expanded Data" = Table.ExpandTableColumn(#"Kept First Rows", "Data", List, List2)

List = List.Union(List.Transform(#"Kept First Rows"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Kept First Rows", "Data", List,List)

List = List.Union(List.Transform(#"Kept First Rows"[Data], each Table.ColumnNames(_))),
List2 = List.Transform (List, each "Data."&_),
#"Expanded Data" = Table.ExpandTableColumn(#"Kept First Rows", "Data", List,List2)