使用电源查询合并Excel中的表

由于每个下拉列表都是不同的表格，因此您必须创建一个包含所有表格的列表。

创建供 PQ 操作的表列表可能很棘手。

在我的示例中，它们被命名为 Table19 - table26，因此我使用了 List.Generate 函数来执行此操作。但是还有其他方法可以生成这样的列表，包括对其进行硬编码。

然后只需阅读所有表格，旋转和扩展即可。

查看代码中的注释以获得更好的理解。

@H_574_38@m 代码

let
    //create a list of the relevant table names
    //  and create a column of the names
    //many ways to do this
    //Since mine are table19-26,I do it like this:

    tablenames = List.Generate(
            ()=>[Table Name = "Table" & "19",IDX=20],each [IDX] < 28,each [Table Name = "Table" & Text.From([IDX]),IDX = [IDX]+1],each [Table Name]
    ),#"Converted to Table" = Table.FromList(tablenames,Splitter.SplitByNothing(),null,ExtraValues.Error),//Add custom column containing the actual table referred to in column1
//then delete the table names column
    #"Added Custom" = Table.Addcolumn(#"Converted to Table","Tables",each Excel.CurrentWorkbook(){[Name=[column1]]}[Content]),#"Removed columns" = Table.Removecolumns(#"Added Custom",{"column1"}),//add custom column to unpivot each of the tables
//then remove the original tables column and expand the unpivoted column
    #"Added Custom1" = Table.Addcolumn(#"Removed columns","unPivot",each 
            Table.UnpivotOthercolumns([Tables],{},"Type","Subtype")),#"Removed columns1" = Table.Removecolumns(#"Added Custom1",{"Tables"}),#"Expanded unPivot" = Table.ExpandTablecolumn(#"Removed columns1",{"Type","Subtype"},"Subtype"})
in
    #"Expanded unPivot"