Get to know Power Query

Import from an Excel table

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

When you load the sales data into Power Query and apply some transformations, Power Query creates a new worksheet, but the original sales data worksheet stays the same. This makes it easy to experiment with the data without changing the data source.

  1. Notice that there are product categories and sales data worksheets. Open the Sales Data worksheet.

  2. Position the cursor on the Excel table, Select Data > Get & Transform Data > From Table/Range. Excel opens the Power Query Editor with your data displayed in a preview pane.

  3. To display all query tables in the workbook from the Queries pane, select the arrow to the left of the preview pane. The Product Sales table is listed last, then the World Cup Results table, and then the tables used earlier in the tutorial workbook. As you add new tables, they are automatically listed here.

    1. The Query Settings pane shows the transformation steps you have taken. You can preview, change, and delete any steps. Power Query has added a few steps for you. The following table summarizes important features:

      Feature

      Description

      Source

      Defines the source for your data.

      Changed Type

      Power Query interprets the data types. You can adjust these later.

      Home Tab

      Use to preview your data and make transformations.

      Close & Load

      When finished, select to return the data to the worksheet.

      Transform Tab

      Provides advanced transformation options.

      Add Column

      Calculates data from existing columns, such as the day of the week from a date or custom calculations.

      View Tab

      Provides additional options, such as opening the Advanced Query Editor.

  4. To return the transformed data to Excel, select Home > Close & Load.

Note   Whether or not you transformed the data, a new worksheet is created. It's a good idea to rename the new worksheet to clearly distinguish it from the original worksheet. Additional transformations are always added to the new worksheet.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.