Power Query: On Error, Goto Line ?

[takoyaki]

Hi All,

I have some M-Code to subtract column values from two tables. The Original table is from a template and starts without any applicable Date Header columns (the quantity of date columns along with their header names varies); it is populated by a macro that copies its values into the Copy table and then refreshes the queries. Upon refresh, the M-code runs perfectly and Delta is populated without errors. However, before the columns are created and populated, Power Query is throwing an error. In VBA, there is a way to skip multiple lines of code and offer an alternative line of code if an error is encountered. Does Power Query have some On Error, Goto Line capability? Or is there some other way to adjust the M-code to evaluate differently if an error is encountered?

Illustration of Data:

M-Code:

let
    Original = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    NonDateHeaders = List.FirstN(Table.ColumnNames(Original),50),
    DateHeaders = List.Skip(Table.ColumnNames(Original),50),
    ReplaceNulls = Table.ReplaceValue(Original,null,0,Replacer.ReplaceValue,DateHeaders),
    Original1 = Table.AddIndexColumn(ReplaceNulls, "Index", 0, 1),
    AllColumnHeaders = Table.ColumnNames(Original1),
    ReorderedList = List.Combine({{"Index"},List.FirstN(AllColumnHeaders,List.Count(AllColumnHeaders)-1)}),
    Original2 = Table.ReorderColumns(Original1,ReorderedList),
    NonDateHeadersI = List.FirstN(Table.ColumnNames(Original2),51),

    Copy = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ReplaceNulls2 = Table.ReplaceValue(Copy,null,0,Replacer.ReplaceValue,DateHeaders),
    Copy1 = Table.AddIndexColumn(ReplaceNulls2, "Index", 0, 1),
    Copy2 = Table.ReorderColumns(Copy1,ReorderedList),

    Destination =  Table.SelectColumns(Original2, NonDateHeadersI),
    UnpivotedOriginal = Table.UnpivotOtherColumns(Table.RemoveColumns(Original2, NonDateHeaders), {"Index"}, "Attribute", "Value"),
    UnpivotedCopy = Table.UnpivotOtherColumns(Table.RemoveColumns(Copy2, NonDateHeaders), {"Index"}, "Attribute", "Value"),
    Source = Table.NestedJoin(UnpivotedOriginal, {"Index", "Attribute"}, UnpivotedCopy, {"Index", "Attribute"}, "Copy", JoinKind.Inner),
    ExpandCopy = Table.ExpandTableColumn(Source, "Copy", {"Value"}, {"Copy.Value"}),
    AddDifference = Table.AddColumn(ExpandCopy, "Difference", each [Copy.Value]-[Value]),
    RemovedColumns = Table.RemoveColumns(AddDifference,{"Value", "Copy.Value"}),
    RepivotColumns = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[Attribute]), "Attribute", "Difference", List.Sum),
    MergeQueries = Table.NestedJoin(RepivotColumns, {"Index"},Destination,{"Index"},"Destination",JoinKind.Inner),
    ExpandFinal = Table.ExpandTableColumn(MergeQueries, "Destination", NonDateHeaders, NonDateHeaders),
    FinalRemoveColumns = Table.RemoveColumns(ExpandFinal, {"Index"}),
    ReorderedList2 = List.Combine({NonDateHeaders, DateHeaders}),
    FinalReorderColumns = Table.ReorderColumns(FinalRemoveColumns,ReorderedList2)

in
    FinalReorderColumns

Ideally, upon encountering an error, I would like the M-Code to skip all subsequent lines of code and return Original instead (so default to whatever the Original table looks like). The error is first encountered at line UnpivotedOriginal (likely because DateHeaders is empty).

Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Details:

 

[sijpie]

Yes.

When VBA encounters an error the error handler steps in. Default is that it stops the macro with an error message. But you can adjust the behaviour. You can even get very sophisticated with it by checking the error number and have different ways of handling each. But let's walk before we run...

Sub ErrorHandling()
    Dim i As Integer
    Dim wsWS As Worksheet
    Dim sReqSheetName
    
    sReqSheetName = "Tiddlywinky"
    
    'ignore the error and continue
    '!!!do this only when you know this is possible, ie you have built in an escape rout later!!!
    On Error Resume Next
    Set wsWS = Sheets(sReqSheetName) 'this will throw an error if the sheet does not exist
    'you can check the error level
    Debug.Print Err  'this will print the error number in the 'immediate window' _
                      (open it through the View menu if it isnt visible bottom left of editor)
    'reset error handling behaviour to default. Always do this when resuming is no longer needed!!!
    On Error GoTo 0
    ' here comes the error check and escape route
    If wsWS Is Nothing Then
        MsgBox "Sheet not found"
        Exit Sub
    End If
    
    'Alternatively you can direct VBA to immediately
    On Error GoTo CleanUp
    Set wsWS = Sheets(sReqSheetName)
    'you can check the error level
    Debug.Print Err
    
    'other code
    
    ' here comes the label where to go to. A label is a name followed by a :
CleanUp:
    'do some cleanup and error messaging
    MsgBox "Required Sheet " & sReqSheetName & " not found"


End Sub

 

[takoyaki]

Yes.

When VBA encounters an error the error handler steps in. Default is that it stops the macro with an error message. But you can adjust the behaviour. You can even get very sophisticated with it by checking the error number and have different ways of handling each. But let's walk before we run...

Sub ErrorHandling()
    Dim i As Integer
    Dim wsWS As Worksheet
    Dim sReqSheetName
   
    sReqSheetName = "Tiddlywinky"
   
    'ignore the error and continue
    '!!!do this only when you know this is possible, ie you have built in an escape rout later!!!
    On Error Resume Next
    Set wsWS = Sheets(sReqSheetName) 'this will throw an error if the sheet does not exist
    'you can check the error level
    Debug.Print Err  'this will print the error number in the 'immediate window' _
                      (open it through the View menu if it isnt visible bottom left of editor)
    'reset error handling behaviour to default. Always do this when resuming is no longer needed!!!
    On Error GoTo 0
    ' here comes the error check and escape route
    If wsWS Is Nothing Then
        MsgBox "Sheet not found"
        Exit Sub
    End If
   
    'Alternatively you can direct VBA to immediately
    On Error GoTo CleanUp
    Set wsWS = Sheets(sReqSheetName)
    'you can check the error level
    Debug.Print Err
   
    'other code
   
    ' here comes the label where to go to. A label is a name followed by a :
CleanUp:
    'do some cleanup and error messaging
    MsgBox "Required Sheet " & sReqSheetName & " not found"


End Sub

Hello, thank you for your response! I think there may have been a misunderstanding - I am aware of the On Error Resume Next / On Error Goto (Line) capability of VBA. I was wondering if there is an equivalent in Power Query - if I encounter an error in a transformation I can skip a number of transformations following it and jump to an alternative transformation instead. Different than try otherwise. Any help would be very much appreciated!

 

[sijpie]

Pass. I am not familiar with powerquery