Starting the query

To start the query, follow these steps:

1. If you have Excel 2016, select the New Query command on the Data tab, and then select From Other Sources ⇒ From Web (see Figure 8-3).

   If you’re working with Excel 2010 or Excel 2013, click the Power Query tab and select the From Web command.

2. In the dialog box that appears, enter the URL for the data you need, as shown in Figure 8-4.

   In this example, you type http://finance.yahoo.com/q/hp?s=MSFT.

   After a bit of gyrating, the Navigator pane shown in Figure 8-5 appears. You can select the data source that you want to extract. Click on each table to see a preview of the data.

3. In this case, Table 4 holds the historical stock data you need, so click Table 4 in the list box on the left and then click the Edit button.

   You may have noticed that the Navigator pane, shown in Figure 8-5, offers a Load button (next to the Edit button). You can use this button to skip any editing and import your targeted data as is. If you’re sure that you won’t need to transform or shape your data in any way, click the Load button to import the data directly into the data model or a spreadsheet in your workbook.

   Excel has another From Web command button, on the Data tab in the Get External Data group. This unfortunate duplicate command is the legacy web-scraping capability found in all Excel versions since Excel 2000.

   The Power Query version of the From Web command (choose New Query ⇒ From Other Sources ⇒ From Web) goes beyond simple web scraping. Power Query can pull data from advanced web pages and then manipulate it. Make sure you’re using the correct feature when pulling data from the web.

   When you click the Edit button, Power Query activates a new Query Editor window, which contains its own Ribbon and a preview pane that shows a preview of the data (see Figure 8-6). You can apply certain actions to shape, clean, and transform the data before importing.

   The idea is to work with each column shown in the Query Editor, applying the necessary actions that will give you the data and structure you need. You can dive deeper into column actions later in this chapter. For now, continue toward the goal of getting the last 30 days of stock prices for Microsoft Corporation.

4. Right-click the Date column to see the available column actions, as shown in Figure 8-7. Select Change Type and then Date to ensure that the Date field is formatted as a proper date.

5. Remove all unnecessary columns by right-clicking each one and selecting Remove.

   (Besides the Date field, the only other columns you need are the High, Low, and Close fields.)

   Alternatively, you can hold down the Ctrl key on the keyboard, select the columns you want to keep, right-click any selected column, and then choose Remove Other Columns (see Figure 8-8).

6. To ensure that the High, Low, and Close fields are formatted as proper numbers, hold down the Ctrl key on the keyboard, select the three columns, and right-click and choose Change Type ⇒ Decimal Number.

   After you do this, you may notice that some of the rows show the word Error. These are rows that contain text values that could not be converted.

7. Remove the error rows by selecting Remove Errors from the Table Actions list (next to the Date field), as shown in Figure 8-9.

8. After all errors are removed, add a Week Of field that displays which week each date in the table belongs to.

   Here’s how to do this:

   1. Right-click the Date field and select the Duplicate Column option.

      A new column is added to the preview.

   2. Right-click the newly added column, select the Rename option, and then rename the column Week Of.

9. Select the Transform tab on the Power Query Ribbon, look to the right to find the Date & Time Column group, and then choose Date ⇒ Week ⇒ Start of the Week, as shown in Figure 8-10.

   Excel transforms the date to display the start of the week for a given date.

10. When you’ve finished configuring your Power Query feed, save and output the results.

    To do this, click the Close & Load drop-down menu on the Home tab of the Power Query Ribbon to reveal the two options shown in Figure 8-11:

    • Close & Load: Saves your query and outputs the results as an Excel table to a new worksheet in your workbook.
    • Close & Load To: Opens the Load To dialog box, where you can choose to output the results to a specific worksheet or to the Data Model. Alternatively, you can choose to save the query only as a query connection, and then you can use the query in various in-memory processes without needing to output the results.

At this point, you have a table similar to the one shown in Figure 8-12, which can be used to produce the pivot table you need.

Take a moment to appreciate what Power Query allowed you to do just now. With a few clicks, you searched the Internet, found some base data, shaped the data to keep only the columns you needed, and even manipulated that data to add an extra Week Of dimension to the base data. This is what Power Query is about: enabling you to easily extract, filter, and reshape data without the need for any programmatic coding skills.

Understanding query steps

Power Query uses its own formula language (known as the “M” language) to codify your queries. As with macro recording, each action you take when working with Power Query results in a line of code being written into a query step. Query steps are embedded M code that allow your actions to be repeated each time you refresh your Power Query data.

You can see the query steps for your queries by activating the Query Settings pane. While in the Query Editor window, you choose View ⇒ Query Settings. You can also place a check mark in the Formula Bar option to enhance your analysis of each step with a formula bar that displays the syntax for the given step.

The Query Settings pane appears to the right of the preview pane, as shown in Figure 8-13. The formula bar is located directly above the preview pane.

Each query step represents an action you took to get to a data table. You can click on any step to see the underlying M code in the Power Query formula bar. For example, clicking the step called Removed Errors reveals the code for that step in the formula bar.

When you click on a query step, the data shown in the preview pane shows you what the data looked like up to and including the step you clicked. For example, in Figure 8-13, clicking the step before the Removed Other Columns step lets you see what the data looked like before you removed the non-essential columns.

You can right-click on any step to see a menu of options for managing your query steps. Figure 8-14 illustrates the following options:

• Edit Settings: Edit the arguments or parameters that defines the selected step.
• Rename: Give the selected step a meaningful name.
• Delete: Remove the selected step. Be aware that removing a step can cause errors if subsequent steps depend on the deleted step.
• Delete Until End: Remove the selected step and all following steps.
• Move Up: Move the selected step up in the order of steps.
• Move Down: Move the selected step down in the order of steps.
• Extract Previous: Create a new query using the steps prior to the selected step. This feature is covered in Chapter 11.

Refreshing Power Query data

Power Query data is in no 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 doesn’t automatically keep up with the changes; you need to intentionally 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 on the table and selecting the Refresh option.

If you chose to load your Power Query data to the Internal Data Model, you need to open the Power Pivot window, select your Power Query data, and then click the Refresh command on the Home tab of the Power Query window.

To get a bit more automated with the refreshing of queries, you can configure your data sources to automatically refresh the Power Query data. To do so, follow these steps:

1. From the Data tab on the Excel Ribbon, select the Connections command.

   The Workbook Connections dialog box appears.

2. Select the Power Query data connection you want to refresh and then click the Properties button.

   The Properties dialog box opens.

3. Select the Usage tab.
4. Set the options to refresh the chosen data connection:
   • Refresh Every X Minutes: Tells Excel to automatically refresh the chosen data every specified number of minutes. Excel refreshes all tables associated with that connection.
   • Refresh Data When Opening the File: Tells Excel to automatically refresh the chosen data connection after opening the workbook. Excel refreshes 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 working with the latest data. Of course, setting these options does not preclude the ability the manually refresh the data using the Refresh command on the Home tab.

Managing existing queries

As you add various queries to a workbook, you need a way to manage them. Excel accommodates this need by offering the Workbook Queries pane, which enables you to edit, duplicate, refresh, and generally manage all existing queries in the workbook. Open the Workbook Queries pane by selecting the Show Queries command on the Data tab of the Excel ribbon.

You need to find the query you want to work with and then right-click it to take any one of the actions described in the following list (see Figure 8-15):

• Edit: Open the Query Editor, where you can modify the query steps.
• Delete: Delete the selected query.
• Refresh: Refresh the data in the selected query.
• Load To: Activate the Load To dialog box, where you can redefine where the selected query’s results are used.
• Duplicate: Create a copy of the query.
• Reference: Create a new query that references the output of the original query.
• Merge: Merge the selected query with another query in the workbook by matching specified columns.
• Append: Append the results of another query in the workbook to the selected query.
• Send to Data Catalog: Publish and share the selected query via a Microsoft Power BI server that your IT department sets up and manages.
• Move to Group: Move the selected query into a logical group that you create for better organization.
• Move Up: Move the selected query up in the Workbook Queries pane.
• Move Down: Move the selected query down in the Workbook Queries pane.
• Show the Peek: Show a preview of the query results for the selected query.
• Properties: Rename the query and add a friendly description.

The Workbook Queries pane is especially useful when your workbook contains several queries. Think of it as a kind of table of contents that allows you to easily find and interact with the queries in your workbook.