Awesome Way To Log in Power Query M Using Diagnostics.Trace

Several authors have described how to use the M function Diagnostics.Trace to monitor the performance of queries built with Power Query. In this installment, we will look at how to use the same function to record row-level error information in the trace log.

Regardless of how we intend to use Diagnostics.Trace, nothing at all can happen until tracing is enabled. This is done by going to the File tab in the Query Editor and selecting Options and Settings | Query Options.

queryoptionsmenu

In the dialog box, simply check Enable tracing. Conveniently, the dialog box also provides a link that will open the folder where trace files will be recorded.

enabletracing

Entire functions (and by implication the query itself) can be traced by invoking the function within Diagnostics.Trace.

let

Source = Csv.Document(File.Contents(“C:\Temp\SeriesReport-20160929125851_79e60f.txt”),[Delimiter=” “, Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None]),

DT = Diagnostics.Trace(TraceLevel.Error, “SeriesReport Error”, ()=>Source, true)

in

DT

Note that the third argument is a function; this is the function that will be traced. To record information about individual rows in the trace file, we can move Diagnostics.Trace into a try otherwise block in a custom column. Since it would be valuable to record a row index number for traced rows, we will also create new index column.

The complete M script looks like this:

let

Source = Csv.Document(File.Contents(“C:\Temp\SeriesReport-20160929125851_79e60f.txt”),[Delimiter=” “, Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None]),

#”Promoted Headers” = Table.PromoteHeaders(Source),

#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Year”, Int64.Type}, {“Jan”, type number}, {“Feb”, type number}, {“Mar”, type number}, {“Apr”, type number}, {“May”, type number}, {“Jun”, type number}, {“Jul”, type number}, {“Aug”, type number}, {“Sep”, type number}, {“Oct”, type number}, {“Nov”, type number}, {“Dec”, type number}}),

#”Added Index” = Table.AddIndexColumn(#”Changed Type”, “Index”, 0, 1),

#”Added Custom” = Table.AddColumn(#”Added Index”, “Custom”, each try[Jul] otherwise Diagnostics.Trace(TraceLevel.Error, “Row Number: ” & Text.From([Index]), ()=>42, true))

in

#”Added Custom”

Note that, as before, the third argument for Diagnostics.Trace must be in the form of a function. Diagnostics.Trace 

#”Added Custom” = Table.AddColumn(#”Added Index”, “Custom”, each try[Jul] otherwise Diagnostics.Trace(TraceLevel.Error, “Row Number: ” & Text.From([Index]), ()=>42, true))

Here we returned 42 for illustrative purposes, ( ) => null would probably be more practical, and it should be clear that any function returning a suitable datatype could be used instead.

In addition, we observe that we can use column values in the error row to construct a custom trace message. In this example, we simply return the row index. Note that we must use the “&” to concatenate text types and that the row index must be explicitly converted to text; there are no implicit datatype conversions.

You can download the workbook ErrorHandling.xlsx along with the example text data source file. Of course, before you can successfully run the queries you must edit the files to point to the location of the data source on your machine. As is, the queries expect to find the file in C:\Temp.

Conclusion

Hence, the Power Query Formula Language, or “M” offers precious little in the way of error handling or debugging. Judicious use of Diagnostics.Trace can provide valuable feedback about M script execution.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.