let Source = Folder.Files("C:\directory\subdirectory"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),

// get all data from all XLS files
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetFileData", each Excel.Workbook([Content],true)),
#"Expanded GetFileData" = Table.ExpandTableColumn(#"Added Custom", "GetFileData", {"Data", "Hidden", "Item", "Kind", "Name"}, {"Data", "Hidden", "Item", "Kind", "Sheet"}),
// filter for specific sheet
#"Filtered Rows1" = Table.SelectRows(#"Expanded GetFileData", each ([Sheet] = "Sheet1")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Content", "Hidden", "Item", "Kind"}),

//demote, then combine first two rows of each column, then recombine with original data
#"Added Custom1" = Table.AddColumn(#"Removed Columns","Top", each Table.TransformColumnTypes(Table.Transpose(Table.FirstN(Table.DemoteHeaders([Data]),2)),{{"Column1", type text}, {"Column2", type text}})),
#"Added Custom2" = Table.AddColumn(#"Added Custom1" ,"Bottom", each Table.Transpose(Table.CombineColumns([Top] ,{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"))),
#"Added Custom3" = Table.AddColumn(#"Added Custom2" ,"Data3", each Table.PromoteHeaders( [Bottom]  & Table.Skip(Table.DemoteHeaders([Data]),2))),

// expand all columns and remove extras
List = List.Union(List.Transform(#"Added Custom3"[Data3], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom3", "Data3", List,List),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Data",{"Data", "Top", "Bottom"})
in #"Removed Columns1"

这就是我要做的

1. 打开目录中的所有 xlsx 文件(根据需要更改扩展名和路径)

1. Opens all the xlsx files in the directory (Change extension and path as needed)

读取每个文件中所有选项卡的所有列

Reads in all columns on all tabs in each file

在每个数据表中，组合每列的前两行，然后与下的数据

In each data table, combines first two rows of each column then combines with the data under that

然后展开所有表