批量应用Power Query至400个Excel文件的三类技术问题咨询

1. 指定要使用的工作表 #

可以直接在Power Query中指定目标工作表，无论处理当前工作簿还是外部文件：

• 处理外部文件时：通过Excel.Workbook()加载文件后，按名称筛选工作表。示例M代码：

  let
      Source = Excel.Workbook(File.Contents("C:\YourFolder\SampleFile.xlsx")),
      TargetSheet = Source{[Name="Master"]}[Data] // 精准定位"Master"工作表
  in
      TargetSheet
  

• 修改现有查询：找到选择工作表的步骤，将索引（如{0}）替换为上述名称引用即可。

2. 自动处理未创建表格的工作表 #

Power Query不需要源工作表是Excel表格格式就能加载数据，它会自动识别工作表的已使用区域并将其作为结构化数据导入：

• 加载数据时，选择「从工作表」选项（或用上述M代码中的[Data]属性），直接抓取工作表的全部已使用区域，无需预先创建表格。
• 若你需要在源文件中实际创建Excel表格（而非仅在Power Query中加载结构化数据），Power Query本身无法修改源文件结构，需搭配VBA脚本实现。示例VBA代码：

  Sub ConvertRangeToTable()
      Dim targetSheet As Worksheet
      Set targetSheet = ThisWorkbook.Sheets("Master")
      targetSheet.ListObjects.Add(xlSrcRange, targetSheet.UsedRange, , xlYes).Name = "MasterTable"
  End Sub
  

3. 批量处理400个文件 #

完全可以通过Power Query的「从文件夹」功能实现批量处理，无需逐个打开文件：

1. 创建基础查询：先编写好处理单个文件的查询（包含你已有的数据转换逻辑），将硬编码的文件路径替换为参数（通过「主页 > 管理参数」添加FilePath参数）。
2. 加载文件夹列表：点击「数据 > 获取数据 > 从文件 > 从文件夹」，选择存放400个文件的文件夹，加载所有文件的路径列表。
3. 批量调用基础查询：添加自定义列，用公式=YourBaseQuery([Folder Path] & [Name])将基础查询应用到每个文件。
4. 展开结果：点击自定义列的展开按钮，将所有文件的转换结果合并为一个表格。

此方法可一次性处理所有文件，后续刷新查询即可同步新文件的转换结果。

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