If you’re using DirectQuery mode for one or more tables in your Power BI dataset, the chances are that you will want to see the SQL (or whatever query language your DirectQuery data source uses) that is generated by Power BI when your report is run. If you can view the queries that are run in the tooling for the data source itself, for example, using Extended Events or SQL Server Profiler for SQL Server, then great – but you may not have permissions to do this. The good news is that you can capture the SQL queries in Power BI Desktop too, even though it’s not always obvious how to do so.
For some data sources like SQL Server then Performance Analyzer will give you the SQL queries generated. All you need to do is go to the View tab in the main Power BI Desktop window, click on the Performance Analyzer button to display the Performance Analyzer pane, click on Start Recording and then Refresh Visuals, find the event corresponding to the visual whose queries you want to view, expand it and then click on the “Copy query” link:
This will copy the DAX query generated by your visual to the clipboard; in the case of SQL Server DirectQuery sources you’ll also get the SQL query generated for that DAX query.
However this method does not work for all DirectQuery data sources; for them you’ll need to use the Query Diagnostics functionality in the Power Query Editor. You need to open the Power Query Editor window, go to the Tools tab on the ribbon, click on the Start Diagnostics button, go back to the main Power BI window, refresh your visuals (you can use the Refresh visuals button in Performance Analyzer for this again) and then go back to the Power Query Editor and click the Stop Diagnostics button. When you do this several new Power Query queries will appear which contain diagnostics data. Go to the one that has a name that starts with “Diagnostics_Detailed” and somewhere in there – where exactly depends on the data source – you’ll find the query generated. For example, for a Snowflake data source you’ll see the SQL generated somewhere in the Data Source Query column:
For an Azure Data Explorer DirectQuery data source the KQL query will be in one of the Record values in the Additional Info column:
One thing to watch out for is that you may also see what look like SQL Server TSQL queries, even when you’re not using a data source that can be queried with TSQL. Here’s an example from the Azure Data Explorer example above:
You can ignore these queries: they’re not useful, although they do give you an interesting insight into how DirectQuery mode works behind the scenes.
The Query Diagnostics from the Power Query Editor never captured my Direct Query. Too bad because this method is much simpler! Maybe because I am not an admin? There’s a note about that in the Options where you turn on tracing.
I was able to follow these steps instead to get the SQL query: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-troubleshoot#review-trace-files
If you don’t have SSMS/SQL Profiler installed, you can just open the trace file in Notepad and the queries are in plain text.
Hi Chris. I’m trying this out for a report that uses DQ over Snowflake, and when I refresh visuals then go back to click “Stop Diagnosis” in PQ I get a message “No evaluations were found in the diagnostics recording”.
(When I click the Refresh All button in PBI, I get the expected tables, but the Data Source Query field contains nulls)