Power Query中转置列内嵌套表后再展开的技术实现问题

报错根源 #

最后一步的Csv.Document(_)是问题核心：Custom.Data列的每个单元格已经是表类型，但Csv.Document函数仅接受文本或二进制数据作为输入，传入表自然触发类型不匹配的报错。

前面的步骤里，Excel.Workbook([Content])已经完成了Excel文件的解析，展开后的Custom.Data就是每个工作表对应的表数据，完全不需要再用Csv.Document二次解析。

正确实现步骤 #

直接对嵌套的Custom.Data表执行转置，之后再展开该列即可。修改后的完整M代码如下：

Source = SharePoint.Files("https://jenson1.sharepoint.com/sites/Portofolio/", [ApiVersion = 15]),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"Content", "Name", "Folder Path", "Extension", "Date accessed", "Date modified", "Date created", "Attributes"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each Text.Contains([Folder Path], "/Completed Investments/Active/")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Extension] = ".xls" or [Extension] = ".xlsm" or [Extension] = ".xlsx")),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "Database_Management_Accounts")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows2",{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Custom.Data"}),
    // 直接转置嵌套表，移除错误的Csv.Document调用
    #"Transposed Nested Tables" = Table.TransformColumns(#"Removed Other Columns1", {{"Custom.Data", each Table.Transpose(_)}}),
    // 展开转置后的表列
    #"Expanded Transposed Data" = Table.ExpandTableColumn(#"Transposed Nested Tables", "Custom.Data", Table.ColumnNames(#"Transposed Nested Tables"{0}[Custom.Data]))
in
     #"Expanded Transposed Data"

关键修改点 #

1. 将Table.Transpose(Csv.Document(_))替换为Table.Transpose(_)，直接对已有的嵌套表执行转置
2. 添加#"Expanded Transposed Data"步骤，自动展开转置后的表列（Table.ColumnNames(#"Transposed Nested Tables"{0}[Custom.Data])用于动态获取转置后的列名）

内容的提问来源于stack exchange，提问作者Jeffrey Faustin