Sign in or start a free trial to avail of this feature.
11. Editing Queries and Loading to Power BI
You will generally want to make changes to your query at a later time. We’ll discuss how to edit queries safely, and load the query output into Power BI Desktop, in this lesson.
Editing a Query
- Every step in the query is recorded in the Applied Steps pane to the right of the screen
- You can edit, rename or reorder the query steps within the Applied Steps pane
- You should take care when doing this, as you can break the query by interfering with the sequence of steps
- When we are finished with our query, we can load the data into Power BI Desktop
- Close and Apply closes the Query Editor and applies the changes made to the Power BI data model
- We can also Close the Query Editor without applying the changes or apply changes without closing the Query Editor
Through this course, we've built up a fairly large query.
In this lesson, we'll see how to edit this query, and then load it into Power BI.
As we built the query, the various steps have been added to the applied steps area on the right of the screen. This is a complete record of all the actions we've taken on the data set. If we select a step, we'll see how the query looked after that step.
By clicking through successive steps, we can effectively replay all the actions we've taken. This could be very handy if you're returning to a query you haven't used for some time.
Certain steps have a cog symbol next to them. This allows you to edit the step in question. For example, let's select the step filtered rows, and then select the corresponding cog symbol. The filter window we used in that step appears referring to the stage where we filtered the data to show only transactions from the month of August. If necessary, we can modify the filter we used. We won't make any changes right now, so we'll select cancel.
You can also see several more editing options by right-clicking on a step. You can rename a step, move it up or down in the query, or insert additional steps. Finally, you can delete any step by clicking the X next to it.
Editing a query via the applied steps window is easy, but it comes with some risks. Let's try to delete the replaced values step. In this step, we replace the number eight in the month column with the word August. As we click the X, we get a warning that this may affect subsequent steps, and potentially break the query.
If subsequent steps use the value August from the month column, then deleting this step would break the query. Therefore, when editing, moving, or deleting a step, you should be sure that you understand the impact the step has on any subsequent steps. We'll select cancel and leave the query as is.
Now that we're happy with our query, let's load it into Power BI. We can do this by navigating to the home tab, and selecting close and apply. From here, we can close the query editor and load the data set to Power BI, apply the changes without closing the query editor, or close the query editor without applying the changes. We'll go with the first option.
If we look at the fields pane, we can see that the two queries have both been loaded into Power BI desktop.
If we go into data view and click on the sales table, we can see that it has all the fields that were visible in the query editor.
Since we merged the quantity data into the sale query, we do not need to see the quantity table in the data model. As such, we'll navigate to the fields pane, right-click on the quantity table, and select hide.
The table is now grayed out, and does not appear at all when we switch to report mode. Note that we should not delete the quantity table completely as doing so would break the sales query at the step where the two queries are merged. We've now seen how you can edit queries in the query editor and then load them into the Power BI data model. In the next lesson, we'll see what happens when your data source updates.