Part V: Understanding Power Pivot and Power Query
Viewing the Advanced Query Editor
Power Query gives you the option of viewing and editing a query’s embedded M code
directly. While in the QPower Query Editor window, click the View tab of the Ribbon and
select Advanced Editor. The Advanced Editor dialog box is little more than a space for you to
edit the existing M code or type your own M code. Advanced users can use the M language
to extend the capabilities of Power Query by directly coding their own steps in the Advanced
Editor. We’ll touch on the M language in Chapter 39, “Transforming Data with Power Query.”
Refreshing Power Query data
It’s important to note that Power Query data is not in any way connected to the source
data used to extract it. A Power Query data table is merely a snapshot. In other words, as
the source data changes, Power Query will not automatically keep up with the changes; you
intentionally need to refresh your query.
If you chose to load your Power Query results to an Excel table in the existing workbook,
you can manually refresh by right-clicking the table and selecting the Refresh option.
If you chose to load your Power Query data to the internal data model, you need to click
Data ➪ Queries & Connections and then right-click the target query and select the Refresh
option.
To get a bit more automated with the refreshing of your queries, you can configure your
data sources to refresh your Power Query data automatically. To do so, follow these steps:
- Go to the Data tab in the Excel Ribbon, and click the Queries & Connections
command. The Queries & Connections task pane appears.
- Right-click the Power Query data connection that you want to refresh and then
select the Properties option.
- With the Properties dialog box open, select the Usage tab.
- Set the options to refresh the chosen data connection:
Refresh Every X Minutes Placing a check next to this option tells Excel to refresh the
chosen data automatically every specified number of minutes. Excel will refresh all tables
associated with that connection.
Refresh Data When Opening the File Placing a check next to this option tells Excel to
refresh the chosen data connection automatically upon opening the workbook. Excel will
refresh all tables associated with that connection as soon as the workbook is opened.
These refresh options are useful when you want to ensure that your customers are work-
ing with the latest data. Of course, setting these options does not preclude the ability to
refresh the data manually.
