-
Book Overview & Buying
-
Table Of Contents
-
Feedback & Rating

Microsoft Power BI Cookbook
By :

The Power Query M engine is an extremely powerful and fast data transformation and data preparation engine used across an array of products, including:
While both fast and powerful, there are times when you may find that a particular query is not as performant as desired. In these instances, Query Diagnostics can help you pinpoint problematic expressions and better understand what Power Query is doing in order to identify areas for query optimization. This recipe demonstrates how the user can use Query Diagnostics to troubleshoot a query and identify how the query might be optimized to be more performant.
To get ready, import the FactCallCenter
table from the AdventureWorksDW2019 database by doing the following:
AdWorksDW
. This query should be similar to the following:
let
Source = Sql.Database("localhost\MSSQLSERVERDEV", "AdventureWorksDW2019")
in
Source
AdWorksDW
and choose Reference; select the FactCurrencyRate table in the data preview area, and rename the AdWorksDW query to FactCurrencyRate.Figure 2.46: Query Diagnostic options
For additional details on performing these steps, see the Managing Queries and Data Sources recipe in this chapter.
To implement this recipe, perform the following steps:
let
Source = AdWorksDW,
dbo_FactCurrencyRate = Source{[Schema="dbo",Item="FactCurrencyRate"]}[Data],
Sort =
Table.Sort(
dbo_FactCurrencyRate,
{{"CurrencyKey", Order.Ascending}, {"DateKey", Order.Ascending}}
),
Index =
Table.TransformColumnTypes(
Table.AddIndexColumn(Sort, "Row Index", 1, 1),
{{ "Row Index", Int64.Type }}
),
PrevIndex =
Table.TransformColumnTypes(
Table.AddIndexColumn(Index, "Prev Index", 0, 1),
{{ "Prev Index", Int64.Type }}
),
SelfJoin =
Table.NestedJoin(
PrevIndex, {"Prev Index"}, PrevIndex, {"Row Index"},
"NewColumn", JoinKind.LeftOuter
),
PrevColumns =
Table.ExpandTableColumn(
SelfJoin, "NewColumn",
{"EndOfDayRate", "CurrencyKey"}, {"PrevRate", "PrevKey"}
),
AddChange =
Table.AddColumn(
PrevColumns, "Daily Change", each
if [CurrencyKey] = [PrevKey] then [EndOfDayRate] - [PrevRate]
else null, type decimal
),
SelectColumns =
Table.SelectColumns(
AddChange,
{"CurrencyKey", "EndOfDayRate", "Date", "Daily Change"}
)
in
SelectColumns
Figure 2.47: Diagnostics processing display
Figure 2.48: Diagnostics queries
Figure 49: Diagnostic results
Table.SelectRows(#"Changed Type", each [Step] = "PrevColumns")
.At this point, it should be evident that this query would likely be better done in the source database system as a view or stored procedure.
The FactCurrencyRate query joins the base table with itself in order to compare subsequent rows of data. The goal is to compare the values of one row with the previous row in order to compute a value for a change in currency rate between days. This is done by sorting the table by first the currency and then the date using the Table.Sort
function. Next, the Table
.AddIndexColumn
function is used twice, once to add an index column starting from 1 (Row Index column) and a second time to add an index column starting from 0 (Prev Index column). These two index columns are then used in a Table
.NestedJoin
function to join the table to itself. The Table
.ExpandTableColumn
function is used to expose the previous row's CurrencyKey and EndOfDayRate columns of the previous row. This information can then be used to create the Daily Change column using the Table
.AddColumns
function, and finally, only the essential columns are selected using the Table
.SelectColumns
function.
When you start Query Diagnostics, query diagnostic information is logged to JSON and CSV files stored in the application's directory. These files can be located by looking at the Source step for aggregated, detailed, and partition diagnostic queries and the CsvFiles step of the Counters diagnostic query. For the Power BI Desktop Store App, the path should be similar to the following:
C:\Users\[user]\Microsoft\Power BI Desktop Store App\Traces\Diagnostics
These files record diagnostic information performed by the query engine during processes such as refreshing the preview data. Stopping Query Diagnostics ends logging of the diagnostic data and generates queries for each enabled Query Diagnostics feature: Aggregated, Detailed, Performance counters, and Data privacy partitions. As diagnostic logging is costly in terms of performance and system resources, it is recommended to only use Query Diagnostics when troubleshooting a query's performance. In addition, only enable the minimal amount of diagnostic logging required to identify the problem—for example, often just starting with the Aggregated diagnostic data is enough to identify the problematic step(s).
There is also a Diagnose Step feature available for Query Diagnostics. To see how Diagnose Step can be used, follow these steps:
select [_].[CurrencyKey],
[_].[DateKey],
[_].[AverageRate],
[_].[EndOfDayRate],
[_].[Date]
from [dbo].[FactCurrencyRate] as [_]
order by [_].[CurrencyKey],
[_].[DateKey]
It should be evident that diagnosing a single step of a query is faster and consumes fewer system resources than analyzing the entire query. Thus, it is recommended that you run only Aggregated diagnostics over an entire query to identify problematic steps, and then run Detailed diagnostics on those steps individually. If you look at the FactCurrencyRate_SelfJoin_Detailed, the most expensive operation is on line 60, and it is the DbDataReader operation. The SQL statement identified is actually on line 59 with the operation Execute Query. Thus, we can conclude that the most expensive operation performed was not in executing the query, but rather reading the data generated by the query.