Quick example on how to handle errors in Power Query along with VBA.

VBA is (still) a powerful language when working with Excel files, and error handling with VBA is relatively easy. You can easily add the following line of code and be very confident that it will catch 99% of the errors you can’t predict:

But what happens when we have Power Query in the mix? VBA can only work when an error occurs outside on PQ environment. If you need to perform any error handling which involves PQ, you must do it on the PQ side, and knowledge of the M-language will go a long way.

Here is an example: you want to know Sales ID on Customer ID, Order Number and City, but Sales ID might not exist.

Quick Code in Power query:

But if SalesID does not exist, you will get this message:

You can not work with this error on the VBA side ( if you know the way, please share!).

Here is what I did to handle this situation: I added a step to check if the #” Removed Other Columns” step will return an empty table. If it does, I then inserted a row with a 0 value; if it does not, give me the table instead.

CheckEmpty = if Table.IsEmpty(#"Removed Other Columns") then Table.InsertRows(#"Removed Other Columns",0,{[ProgrammedSalesID = 0]}) else #"Removed Other Columns",        

Final Code:

Now that I have 0 in the Excel sheet, VBA can deliver the message to the user.

If Sheets("Sales").Range("ProgrammedSalesID").Value = 0 The
    MsgBox "There is no Sales Person associated with this order number!", Title:=" Error occuried", Buttons:=vbExclamation
    Exit Sub
End Ifn        

And this is it!