修改Power Query代码实现批量读取同格式PDF文件夹

核心思路 #

把原有的单个PDF处理逻辑封装成自定义函数，然后遍历目标文件夹里的所有PDF文件，逐个应用函数并合并最终结果。

修改后的完整代码 #

let
    // 1. 定义处理单个PDF的自定义函数
    ProcessPDF = (filePath as text) as table =>
        let
            Source = Pdf.Tables(File.Contents(filePath), [Implementation="1.3"]),
            Page1 = Source{[Id="Page001"]}[Data],
            #"Promoted Headers" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true]),
            #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"[image]", type text}, {"TestHeader", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
            #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column5", "Column6", "Column8", "Column10"}),
            #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each not Text.StartsWith([#"TestHeader"], "MyOff")),
            #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"[image]", "Column2"}, {"TestHeader", "Column3"}, {"Column7", "Column5"}, {"Column9", "Column6"}}),
            Page2 = Source{[Id="Page002"]}[Data],
            #"Changed Type2" = Table.TransformColumnTypes(Page2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
            #"Removed Columns2" = Table.RemoveColumns(#"Changed Type2",{"Column5"}),
            #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Column6", "Column5"}, {"Column7", "Column6"}}),
            #"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns2"}),
            #"Added Custom" = Table.AddColumn(#"Appended Query", "Test_Date", each Record.Field(#"Appended Query"{0},"Column3")),
            #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each Text.StartsWith([Column1], "NF")),
            #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows1",{{"Column1", "Wells"}, {"Column2", "Wet"}, {"Column3", "Hours"}, {"Column4", "FTHP_p"}, {"Column5", "FTHP_b"}, {"Column6", "Comments"}})
        in
            #"Renamed Columns1", // 修正原代码中的笔误
            
    // 2. 读取目标文件夹中的所有PDF文件
    FolderSource = Folder.Files("C:\Your\Target\Folder\Path"), // 替换为你的文件夹路径
    FilterPDFs = Table.SelectRows(FolderSource, each [Extension] = ".pdf"),
    
    // 3. 为每个PDF文件添加处理结果列
    AddProcessedColumn = Table.AddColumn(FilterPDFs, "ProcessedData", each ProcessPDF([Folder Path] & [Name])),
    
    // 4. 展开处理结果并保留文件名（可选，用于溯源）
    ExpandProcessedData = Table.ExpandTableColumn(AddProcessedColumn, "ProcessedData", {"Wells", "Wet", "Hours", "FTHP_p", "FTHP_b", "Comments", "Test_Date"}, {"Wells", "Wet", "Hours", "FTHP_p", "FTHP_b", "Comments", "Test_Date"}),
    KeepUsefulColumns = Table.SelectColumns(ExpandProcessedData, {"Name", "Wells", "Wet", "Hours", "FTHP_p", "FTHP_b", "Comments", "Test_Date"})
in
    KeepUsefulColumns

关键修改说明 #

• 自定义函数封装：把原单文件处理逻辑打包成ProcessPDF函数，参数为文件路径，保证每个文件独立处理
• 文件夹读取与过滤：用Folder.Files获取文件夹内容，通过Table.SelectRows筛选出PDF格式文件
• 批量处理与合并：通过Table.AddColumn调用函数处理每个文件，再用Table.ExpandTableColumn展开所有结果并合并
• 溯源信息保留：保留原文件名列，方便后续排查数据来源
• 笔误修正：修复原代码中in #"Renamed Column1"的笔误，改为#"Renamed Columns1"

使用注意事项 #

1. 替换代码中的"C:\Your\Target\Folder\Path"为实际的PDF文件夹路径
2. 确保所有PDF文件格式与原测试文件完全一致（页面ID、列结构等）
3. 若部分PDF页数或结构不同，可添加try...otherwise错误处理逻辑避免批量处理中断

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