Hopefully, this blog will quickly become outmoded and useless and I will have wasted my time.
Error handling in the Power Query Formula Language, most often simply called “M”, is weak compared with most modern programming environments, and the developers at Microsoft will no doubt address these issues and provide a better system. But, for better or for worse, we must deal with Power Query error handling the way it is now. The most commonly used technique is really not error handling at all as much as it is error reporting.
We’ll start by importing a text file as we did in the previous installment. In that example, annotations added to the most recent cell data prevented that data from being successfully converted into a numeric datatype. In this example, we will explore those errors.
We add a new custom column to our query using the try statement. Later, we will use try along with its sister statement otherwise. The statement try, by itself, returns a value if successful and an error record structure if an error occurs.
In the Query Editor, we can expand the new record column by clicking on the small icon in the upper right. We want error, which is another record in its own right, so we expand a second time. This technique makes it easy to see which rows have errors in the Jul column, and what those errors are. Of course, when we load the data into Excel, these new error columns will be loaded as well. Of course this may be what you want. However, it is likely that the presence of these extra columns will be of little value to most Excel users. If we simply remove the columns using Power Query before importing, then the columns are simply gone and we cannot refer to them again.
There is a simple solution to keeping error columns in Power Query where we might wish to view them, but not displaying them in Excel. When you click “Close and Load”, the last task of Power Query is to create a query in the Excel table object that reads the data from the (far more complex) query generated by Power Query. We can edit this query if we wish. Right-clicking on a table provides a context menu from which we choose “Table”. The submenu lists “Edit query…” as one of the choices. The simple SELECT statement retrieves all the columns from the Power Query query. This query can be edited to return only those columns you want. Editing the Excel table query has no effect at all on the definition of the Power Query.
Of course, any developer worth their salt wants to find a shortcut to typing a bunch of column names. Unfortunately, you cannot copy the column headings you want from the workbook and paste them into the query, since what you are copying are actually cells, not text, and the simplistic Edit OLE DB Query dialog does know what to do. There is nothing, however, to prevent you from copying the cells, pasting into Notepad, then copying the text from Notepad and pasting into the Edit OLE DB Query window. You will still have to type the commas to separate the column names.
Tracking individual column values that cause errors is straightforward in Power Query. In the next installment, we will make a more sophisticated application of try otherwise to implement the logging of rows with problem data.