Add a query to an Excel worksheet (Power Query)

Important   This article applies to Power Query, a data analysis feature available for Excel that lets you discover, combine and refine data. You may need to enable Power Query in Excel. You can also download and install the most recent version of Power Query for Excel, which automatically enables it. Power Query technology is also built into the Power BI Designer, which is a stand-alone report authoring and data transformation tool for Power BI.

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

In Power Query, you have several options to load queries into your workbook. The Options dialog box lets you set default query load settings.

I need to

Load queries into your workbook

Fine-tune your load options

Load a query to the Excel Data Model

Set default query load settings

Load queries into your workbook

You have 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 contextual Query ribbon tab

    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 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.

To load a query to the Excel Data Model

  1. In the Load To dialog box, check Add the data to the Data Model. To learn how to show the Load To dialog box, see Load queries into your workbook.

Set default query load settings

To set your default query load settings

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

  2. In the Options dialog box, 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 following video 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. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

Applies To: Excel 2010, Excel 2013



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language