The Excel Query Screen
The Excel Query Tab
Previous Session Summary
In the previous lesson, we looked at steps that PowerQuery automatically puts in place including the Source step and the Changed Type step. We then went onto looking at some of the basic transformation steps which we can perform ourselves including Remove Rows and Use First Rows as Headers. We then closed and loaded the data into standard Excel.
Lesson Synopsis
At the end of the last lesson we loaded our PO data back into Excel. In this lesson, we look at the Query tab which appears in a standard Excel workbook in which a query has been created. The Query tab has options for getting back into the PowerQuery editor from Standard Excel as well as how to create notes on what our queries do, delete queries and refresh queries.
Resource Listing
The Video
- 01:44 - How can I bring up the Query tab?
- 02:30- How can I get back into the PowerQuery Editor?
- 02:39 - Properties button - make notes on my Query
- 03:08 - Delete button - deleting the query
- 03:22 - Refresh button - Pick up changes in the Source data
Lesson Notes
Once we’ve loaded our data back into Excel from the PowerQuery Editor screen then we have some options that appear for us in standard Excel relating to the PowerQuery Editor.
To access these options, click on the table of data that the PowerQuery Editor produced from our query. This then brings up two new tabs at the top of the Excel screen which are Design and Query. The Design tab simply allows you to format the table of data that’s been produced by PowerQuery. This is like any other Excel formatted table, so we’re not going to dwell on that tab. The Query tab is the tab that we’re interested in.
The Query tab gives you a few options of commands that you can give the PowerQuery Editor but in standard Excel. These are as follows:
- Edit - Click on this button and Excel will return you to the PowerQuery Editor screen
- Properties - Brings up a description box where you can write notes relating to the Query
- Delete - This button deletes the Query
- Refresh - Commands the PowerQuery Editor to perform the recorded commands over all the data in that it finds in the 'Source' step. This allows us to change data in the Source file or table and then ask PowerQuery to do all the cleaning and formatting again with any new or amended information.
- Load to - Changes the way that Excel shows us query information. A table is the default setting but we can also choose other options such as a Pivot table.
- Duplicate, Reference, Merge and Append we deal with in later lessons.
.... and a little more!
Refreshing – You can also opt to refresh the Source dataset through selecting the Data tab and then selecting Refresh