How to view the SQL query for a series of transforms in Query Editor
Did you know that you can view the SQL for a query in Power BI's Query Editor? Me neither!

Posted by Andy Brown on 02 July 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Showing the native SQL query in Query Editor

So here's a thing I didn't know (thanks, Sam).  If you've imported some data into Power BI using Query Editor, you can see the associated SQL query:

Showing native query

Providing that you haven't done anything too outlandish, you can see the native query for the series of transforms that you've applied.

 

This only works if you're loading data from a source which has a native query language, of which SQL Server is one obvious example (if you try to doing the same thing after loading data from Excel the option above is greyed out).

Here's what I got for the above example:

Native SQL

The query for the series of steps above.

When you add a new column, Query Editor sometimes manages to translate it into SQL and sometimes doesn't.  Here's an example of one which did work:

Length of column

I used Column by Example to show the length of each film name, and Query Editor successfully turned this into SQL.

And here's an example of a transform which didn't work:

Greyed out menu

Alas, the Split by Delimiter task was a step too far, and couldn't be translated into SQL.

 

Whether this will actually help anyone, I'm not sure! 

This blog has 0 threads Add post