Using TRY and OTHERWISE in PowerQuery

How can I Combine these two power query codes (Code 1 and Code 2) using try and otherwise functions. I want either one to run if one encounters a problem. Most of the lines are similar just few that are different.

Code 1:

let
    process= (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}, 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",

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"



Code 2:
let
    process= (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",

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"

Solution

try #"Code 1" otherwise #"Code 2"

will return the results of the Code 1 query/variable unless it's an error. Otherwise, it will return the results of the Code 2 query/variable.

So as long as any problems in Code 1 result in an error (as opposed to a valid list, record, or table that contains an error as one of the values), that should work.

For multiple failover scenarios you can chain several of these statements together.

try
  Expression1
otherwise try
  Expression2
otherwise try
  Expression3
otherwise
  Expression4

For more on error handling, the best article I've seen is https://bengribaudo.com/blog/2020/01/15/4883/power-query-m-primer-part-15-error-handling