如何使用Power Query的try和otherwise合并两段PDF处理代码

两段代码核心逻辑一致，仅在类型转换、列重命名和列移除步骤存在差异。我们可以通过Power Query的try...otherwise函数，将两段代码的核心处理逻辑封装，实现优先执行Code1逻辑，若执行失败则自动切换到Code2逻辑的效果。

合并后的完整代码 #

let
    // 封装Code1的处理逻辑
    process_Code1 = (filename)=>
        let Source = Pdf.Tables(File.Contents(filename), [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}, {"test22", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type datetime}}),
            #"Added Custom" = Table.AddColumn(#"Changed Type", "Test_Date", each Record.Field(#"Changed Type"{1},"test22")),
            #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Column1] = "P123" or [Column1] = "P49" or [Column1] = "P610" or [Column1] = "P75" or [Column1] = "P811" or [Column1] = "11-TEST1" or [Column1] = "11-TEST10" or [Column1] = "11-TEST11" or [Column1] = "11-TEST2" or [Column1] = "11-TEST3" or [Column1] = "11-TEST4" or [Column1] = "11-TEST5" or [Column1] = "11-TEST6" or [Column1] = "11-TEST7" or [Column1] = "11-TEST8" or [Column1] = "11-TEST9")),
            #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "TEST444"}, {"[image]", "GGG"}, {"test22", "GGG2"}, {"Column4", "GGG3"}, {"Column7", "GGG4"}, {"Column9", "GGG5"}}),
            #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column5", "Column6", "Column8", "Column10"})
        in  #"Removed Columns",

    // 封装Code2的处理逻辑
    process_Code2 = (filename)=>
        let Source = Pdf.Tables(File.Contents(filename), [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}, {"test22", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type datetime}}),
            #"Added Custom" = Table.AddColumn(#"Changed Type", "Test_Date", each Record.Field(#"Changed Type"{1},"test22")),
            #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Column1] = "P123" or [Column1] = "P49" or [Column1] = "P610" or [Column1] = "P75" or [Column1] = "P811" or [Column1] = "11-TEST1" or [Column1] = "11-TEST10" or [Column1] = "11-TEST11" or [Column1] = "11-TEST2" or [Column1] = "11-TEST3" or [Column1] = "11-TEST4" or [Column1] = "11-TEST5" or [Column1] = "11-TEST6" or [Column1] = "11-TEST7" or [Column1] = "11-TEST8" or [Column1] = "11-TEST9")),
            #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "TEST444"}, {"[image]", "GGG"}, {"test22", "GGG2"}, {"Column4", "GGG3"}, {"Column6", "GGG4"}, {"Column8", "GGG5"}}),
            #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column5", "Column7", "Column9"})
        in  #"Removed Columns",

    // 合并逻辑：优先执行Code1，失败则执行Code2
    process = (filename) => try process_Code1(filename) otherwise process_Code2(filename),

    // 外层文件夹处理逻辑（与原代码一致）
    zSource = Folder.Files("H:\\TEST\\"),
    #"zFiltered Rows" = Table.SelectRows(zSource, each ([Extension] = ".pdf") or ([Extension] = ".PDF")),
    #"zAdded Custom" = Table.AddColumn(#"zFiltered Rows", "Data", each process([Folder Path]&[Name])),
    #"zRenamed Columns" = Table.RenameColumns(#"zAdded Custom",{{"Name", "zName"}}),
    List = List.Union(List.Transform(#"zRenamed Columns"[Data], each Table.ColumnNames(_))),
    #"zExpanded Data2" = Table.ExpandTableColumn(#"zRenamed Columns", "Data", List, List)
in #"zExpanded Data2"

关键说明 #

1. 分离逻辑：将两段代码的核心处理逻辑分别封装为process_Code1和process_Code2函数，保持代码结构清晰。
2. 异常 fallback 实现：定义process函数，通过try process_Code1(filename) otherwise process_Code2(filename)实现自动切换——当Code1因列名不匹配、类型转换失败等问题报错时，会自动执行Code2的逻辑。
3. 修复语法错误：原Code1的Changed Type步骤存在语法错误（多余的type text}），合并时已修正，确保逻辑可正常执行。

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