How to load queries into an Excel worksheet (Power Query)

A blue question mark. Image by Tumisu from Pixabay.

  • Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010.

This post will teach you all about loading queries into an Excel worksheet in Power Query. It spans across all the various actions you need to do in relation to loading queries into an Excel worksheet. These include:

  • Loading queries into your workbook;
  • Fine-tuning your load options;
  • Loading a query to the Excel Data Model; and
  • Setting default query load settings.

Note: Power Query is called Get & Transform in Excel 2016. Information given here relates to both. To learn more, see Get & Transform in Excel 2016.

Note: For a fast video on how to present Query Editor, refer to the end of this post.

Power Query provides multiple options for transferring queries into your workbook. You format predefined query load settings in the Options dialogue 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

Loading queries into your workbook

There are numerous options to load queries into your workbook:

  1. Via Search results:
Using Search results for loading queries into your workbook
  1. With the Navigator pane:
Using the Navigator for loading queries into an Excel worksheet
  1. From the Query Editor:Query Editor
  2. From the Workbook Queries pane and the Query context menu:
Using Workbook Queries for loading queries into an Excel worksheet

Note: When you select Load To from the Workbook Queries pane, you can only Load to worksheet or Load to Data Model. Other load-to options enable you to tweak how you load a query. To learn about the complete range of load options, see How to fine-tune your load options.

Fine-tune your load options for loading queries to an Excel worksheet

Using Power Query Load-To options, you can:

  • Choose how you want to see your data.
  • Decide where the data is loaded.
  • Insert 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 created from several tables within an Excel workbook. Inside Excel, a Data Model is used transparently, offering tabular data used in PivotTables, PivotCharts, and Power View reports.

Using Power Query, data and annotations on the Data Model are retained during the process of adjusting the Load to Worksheet setting of a query. Power Query avoids resetting the query results in both the worksheet and the Data Model when amending either one of the two load settings.

Loading queries into the Excel Data Model

To load a query to the Excel Data Model, simply check the Add the data to the Data Model box. Do this in the Load To popup window. To find out more about how to present the Load To popup window, check the Load queries into your workbook section above.

Set default query load settings for loading queries into an Excel worksheet

Follow these steps to confirm your default query load settings:

  1. In the Power Query ribbon tab, press Options.
  2. In the Options popup window, pick a Default Query Load Setting.

Options

Note: The Query Editor only emerges once you load, edit, or design a new query using Power Query. The video underneath illustrates the Query Editor window displaying when a query has been edited 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, pick From Other Sources > Blank Query.

How to see Query Editor in Excel

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

Leave a Reply

%d bloggers like this: