Add a query to an Excel worksheet (Power Query)

Add a query to an Excel worksheet (Power Query)

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

Note: For a very quick video on how to display Query Editor, see the end of this article.

Power Query offers several options for loading queries into your workbook. You set default query load settings in the Options dialog popup.

I need to:

Load queries into your workbook

Fine-tune my load options

Load a query to the Excel Data Model

Set default query load settings

Load queries into your workbook

There are several options to load queries into your workbook:

  1. From Search results:

    Search results
  2. From the Navigator pane:

    Navigator
  3. From the Query Editor:

    Query Editor
  4. From the Workbook Queries pane and the Query context menu:

    Workbook Queries


Note: When you click Load To from the Workbook Queries pane, you can only Load to worksheet or Load to Data Model. Other load-to options allow you to fine-tune how you load a query. To learn about the full set of load options, see How to fine-tune your load options.

Fine-tune your load options

With Power Query Load-To options, you can:

  • Select how you want to view your data.

  • Select where the data is loaded.

  • Add data to the data model.

Load a query to the Excel Data Model

Note: The steps in this section require Excel 2013.

An Excel Data Model is a relational data source composed from multiple tables inside an Excel workbook. Within Excel, a Data Model is used transparently, providing tabular data used in PivotTables, PivotCharts, and Power View reports.

With Power Query, data and annotations on the Data Model are preserved when modifying the Load to Worksheet setting of a query. Power Query does not reset the query results in both the worksheet and the Data Model when modifying either one of the two load settings.

Load a query into the Excel Data Model

To load a query to the Excel Data Model, in the Load To popup window, check the Add the data to the Data Model box. To learn how to display the Load To popup window, see the Load queries into your workbook section above.

Set default query load settings

Follow these steps to set your default query load settings:

  1. In the Power Query ribbon tab, click Options.

  2. In the Options popup window, choose a Default Query Load Setting.

Options

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The video below shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query: From the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query.


How to see Query Editor in Excel

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×