Sign in or start a free trial to avail of this feature.
12. Updating and Refreshing the Query
One of the key strengths of the Query Editor is that new data can often be added with little, if any, modifications to the query. In this lesson, we’ll see how to add more data and refresh the query.
Refreshing the Query
- Refreshing the Query runs all the steps in the query again
- If your data source updates, the new data will be processed by the query, and the dataset imported to Power BI will update
- Your new data should be in the same format as existing data, otherwise your query may deal with the new data in unexpected ways
- If new data is in the same format as previous data, you should have to make few, if any, changes to the query
Over the previous lessons, we've seen how to build a query and load it into Power BI.
Over the next two lessons, we'll look at how to add new data to the query after it's already been loaded. The data we've used so far runs from 2013 to 2016.
Let's assume that we now get data from 2017 and want to add it to our query. In this lesson, we'll update and refresh our query with new sales data from 2017.
As you can see, sales figures for 2017 are in a single workbook with the same format as the workbooks for 2013 to 2016 sales. We've placed this workbook in the same folder as the workbooks for 2013 to 2016.
We've also appended the existing the existing quantity data workbook to include data for 2017. If you're following along on your own instance of the Power BI query editor, make sure you update the files and folders accordingly. Let's now return to the query editor. We can see the output from the end of our query here. If we select the arrow next to the year column, we can see that the data currently runs from 2013 to 2016. Let's navigate to the home tab and select refresh preview. This should update the query to reflect the new contents of both the sales folder and the quantity file. However, when we select the arrow next to the year column, it still does not include 2017 data. To fix this, we need to edit the query. Let's navigate to the query settings pane and select the expanded data step.
This step is where we extracted monthly columns of sales data from the Excel files.
If we scroll to the right, we can see that columns have only been added for months up to the end of 2016. To fix this, we'll modify this step by selecting the cog next to expanded data. If we scroll to the bottom of this list and click load more, we can see that columns for sales figures in 2017 are unchecked. We'll check all the columns from January to September 2017 and click okay to change this step.
Now our 2017 data will be passed through the query in the same way as the 2013 to 2016 data.
We'll select the final step to see the output of the query.
If we select the arrow at the top of the year column, we can see that the query includes data for 2013 up to 2017.
We've successfully updated our query to include the new data from 2017. As you can see, if the new data is in the same format as the data used to create the query, then updating is particularly easy. You can often update the query simply by refreshing it. In other instances, you may need to slightly modify the query as we did here. There's no need to repeat all the steps in the query itself, as they're automatically applied to the new data. This is one of the main strengths of processing data using the query editor. In the next lesson, we'll see how to update a query when new data is not in the same format as our existing data.