I’ve blogged a few times about the tracing functionality that is built into Power Query/Get&Transform and Power BI (see here and here). The trace files themselves clearly contain a lot of interesting information, but there’s no official documentation about what they contain and the format seems to have changed several times. I guess they are meant for Microsoft internal use but that doesn’t stop us from taking a peek at their contents…
Whenever I’ve looked at the contents of a trace file, one problem I have faced is working out which events relate to the query that I’ve just executed. Today, though, I discovered an M function that can help with this: Diagnostics.ActivityId(). It’s not listed in the online M function reference but here’s the documentation from the function itself:
Calling the function does indeed return “an opaque identifier for the currently-running evaluation”. Using the following code in a query:
…returns the following:
Every time the query is refreshed a different value is returned.
Now, consider the following query that runs a query against a SQL Server database:
let Source = Sql.Database("localhost", "Adventure Works DW"), Test = Value.NativeQuery( Source, "SELECT DISTINCT CalendarYear FROM DimDate") in Test
How can you find the trace events that relate to a particular execution of this query? One way is to add a custom column to this query that returns the value returned by Diagnostics.ActivityId():
let Source = Sql.Database("localhost", "Adventure Works DW"), Test = Value.NativeQuery( Source, "SELECT DISTINCT CalendarYear FROM DimDate"), #"Added Custom" = Table.AddColumn( Test, "ActivityId", each Diagnostics.ActivityId()) in #"Added Custom"
Then, after the query has been loaded into the Data Set you can copy the ActivityID from the table in the Data pane:
…and then search for the same value in the trace files:
Of course you’re now on your own trying to make sense of what you find in the trace file, but I hope this helps a little bit!