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:
[sourcecode language=”text” padlinenumbers=”true”]
Diagnostics.ActivityId()
[/sourcecode]
…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:
[sourcecode language=”text”]
let
Source = Sql.Database("localhost", "Adventure Works DW"),
Test = Value.NativeQuery(
Source,
"SELECT DISTINCT CalendarYear FROM DimDate")
in
Test
[/sourcecode]
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():
[sourcecode language=”text”]
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"
[/sourcecode]
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!
Very nice and helpful