Changing the SQL Statement In Power BI

When working in Power BI one of the things I find myself doing when connecting to a SQL Database data source is I like to write and paste out the SQL query I’m going to use to retrieve my data and this normally works out for me all the time as I can execute the query and validate the results are exactly what I need before doing any work in Power BI.

Recently though I needed to make a change to the SQL logic I had for a report and it actually took me a minute to find where to make that change as it wasn’t located where one expect it to be.

The Report

So here is my report demo. It’s a simple report that shows the high and low temp by day which can be sliced by the Meso Station Name and Month. When I wrote the query for this report I limited my results for only 2018. So lets say I want to change that and include 2019 and 2020. Cool no big deal right?

So I click on the Home tab select Transform data and select Data source settings

Then I am shown the window to manage the data source settings. I select the source and click on Change Source button at the bottom.

The next screen I come to is where I expected to be able to modify the SQL statement but was met with no option to edit the statement as shown below.

Solution

To make a change to the SQL statement you actually have to click on Transform data right above the Data source settings.

This load the Power Query Editor.

In the applied steps portion of the window on the right-hand side of the editor, you will see a listing for Source. To the right of that is a tiny cogwheel which you need to click on. Now the ability to edit the SQL statement is present. I can make the changes I need and click OK. Then simply close & apply and Power BI will update the data.