Add a query to an excel worksheet power query

Power Query offers several ways to create and load Power queries into your workbook. You can also set default query load settings in the Query Options window.   

Tip      To tell if data in a worksheet is shaped by Power Query, select a cell of data, and if the Query context ribbon tab appears, then the data was loaded from Power Query. 

Know which environment you're in Power Query is well-integrated into the Excel user interface, especially when you import data, work with connections, and edit Pivot Tables, Excel tables, and named ranges. To avoid confusion, it's important to know which environment you are currently in, Excel or Power Query, at any point in time.

The familiar Excel worksheet , ribbon, and grid	The Power Query Editor ribbon and data preview

For example, manipulating data in an Excel worksheet is fundamentally different than Power Query. Furthermore, the connected data that you see in an Excel worksheet, may or may not have Power Query working behind the scenes to shape the data. This only occurs when you load the data to a worksheet or Data Model from Power Query.

Rename worksheet tabs    It's a good idea to rename worksheet tabs in a meaningful way, especially if you have a lot of them. It's particularly important to clarify the difference between a worksheet of data, and a worksheet loaded from the Power Query Editor. Even if you have only two worksheets, one with an Excel table, called Sheet1, and the other a query created by importing that Excel table, called Table1, it's easy to get confused. It's always good practice to change the default names of worksheet tabs to names that make more sense to you. For example, rename Sheet1 to DataTable and Table1 to QueryTable. Now it's clear which tab has the data and which tab has the query.

You can either create a query from imported data or create a blank query.

Create a query from imported data

This is the most common way to create a query.

1. Import some data. For more information, see Import data from external data sources.

2. Select a cell in the data and then select Query > Edit.

Create a blank query

You may want to just start from scratch. There are two ways to do this.

• Select Data > Get Data > From Other Sources > Blank Query.

• Select Data > Get Data > Launch Power Query Editor.

At this point, you can manually add steps and formulas if you know the Power Query M formula language well.

Or you can select Home and then select a command in the New Query group. Do one of the following.

• Select New Source to add a data source. This command is just like the Data > Get Data command in the Excel ribbon.

• Select Recent Sources to select from a data source you have been working with. This command is just like the Data > Recent Sources command in the Excel ribbon.

• Select Enter Data to manually enter data. You might choose this command to try out the Power Query Editor independent of an external data source.

Assuming your query is valid and has no errors, you can load it back to a worksheet or Data Model.

Load a query from the Power Query Editor

In the Power Query Editor, do one of the following:

• To load to a worksheet, select Home > Close & Load > Close & Load.

• To load to a Data Model, select Home > Close & Load > Close & Load To.
  
  In the Import Data dialog box, select Add this data to the Data Model.

Tip   Sometimes the Load To command is dimmed or disabled. This can occur the first time you create a query in a workbook. If this occurs, select Close & Load, in the new worksheet, select Data > Queries & Connections > Queries tab, right click the query, and then select Load To. Alternatively, on the Power Query Editor ribbon select Query > Load To.

Load a query from the Queries and Connections pane 

In Excel, you may want to load a query into another worksheet or Data Model.

1. In Excel, select Data > Queries & Connections, and then select the Queries tab.

2. In the list of queries, locate the query, right click the query, and then select Load To. The Import Data dialog box appears.

3. Decide how you want to import the data, and then select OK. For more information about using this dialog box, select the question mark (?).

There are several ways to edit a query loaded to a worksheet.

Edit a query from data in Excel worksheet

• To edit a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit.

Edit a query from the Queries & Connections pane

You may find the Queries & Connections pane is more convenient to use when you have many queries in one workbook and you want to quickly find one.

1. In Excel, select Data > Queries & Connections, and then select the Queries tab.

2. In the list of queries, locate the query, right click the query, and then select Edit.

Edit a query from the Query Properties dialog box

• In Excel, select Data > Data & Connections > Queries tab, right click the query and select Properties, select the Definition tab in the Properties dialog box, and then select Edit Query.

Tip      If you are in a worksheet with a query, select Data > Properties, select the Definition tab in the Properties dialog box, and then select Edit Query. 

A Data Model typically contains several tables arranged in a relationship. You load a query to a Data Model by using the Load To command to display the Import Data dialog box, and then selecting the Add this data to the Data Model check box. For more information about Data Models, see Find out which data sources are used in a workbook data model, Create a Data Model in Excel, and Use multiple tables to create a PivotTable.

1. To open the Data Model, select Power Pivot > Manage.

2. At the bottom of the Power Pivot window, select the worksheet tab of the table you want.
   
   Confirm that the correct table displays. A Data Model can have many tables.

3. Note the name of the table.

4. To close the Power Pivot window, select File > Close. It may take a few seconds to reclaim memory.

5. Select Data > Connections & Properties > Queries tab, right click the query, and then select Edit.

6. When finished making changes in the Power Query Editor, select File > Close & Load.

Result

The query in the worksheet and the table in the Data Model are updated.

If you notice that loading a query to a Data Model takes much longer than loading to a worksheet, check your Power Query steps to see if you are filtering a text column or a List structured column by using a Contains operator. This action causes Excel to enumerate again through the entire data set for each row. Furthermore, Excel can't effectively use multithreaded execution. As a workaround, try using a different operator such as Equals or Begins With.

Microsoft is aware of this problem and it is under investigation.

You can load a Power Query:

• To a worksheet. In the Power Query Editor, select Home > Close & Load > Close & Load.

• To a Data Model. In the Power Query Editor, select Home > Close & Load > Close & Load To.
  
  By default, Power Query loads queries to a new worksheet when loading a single query, and loads multiple queries at the same time to the Data Model.  You can change the default behavior for all your workbooks or just the current workbook. When setting these options, Power Query doesn't change query results in the worksheet or the Data Model data and annotations.
  
  You can also dynamically override the default settings for a query by using the Import dialog box which displays after you select Close & Load To.

Global settings that apply to all your workbooks

1. In the Power Query Editor, select File > Options and settings > Query Options.

2. In the Query Options dialog box, on the left side, under the GLOBAL section, select Data Load.

3. Under the Default Query Load Settings section, do the following:

   • Select Use standard load settings. 

   • Select Specify custom default load settings, and then select or clear Load to worksheet or Load to Data Model.

Tip    At the bottom of the dialog box, you can select Restore Defaults to conveniently return to the default settings.

Workbook settings that only apply to the current workbook

1. In the Query Options dialog box, on the left side, under the CURRENT WORKBOOK section, select Data Load.

2. Do one or more of the following:

   • Under Type Detection, select or clear Detect column types and headers for unstructured sources.
     
     The default behavior is to detect them. Clear this option if you prefer to shape the data yourself.

   • Under Relationships, select or clear Create relationships between tables when adding to the Data Model for the first time.

     Before loading to the Data Model, the default behavior is to find existing relationships between tables, such as foreign keys in a relational database and import them with the data. Clear this option if you prefer to do this on your own.

   • Under Relationships, select or clear Update relationships when refreshing queries loaded to the Data Model.
     
     The default behavior is to not update relationships. When refreshing queries already loaded to the Data Model, Power Query finds existing relationships between tables such as foreign keys in a relational database and updates them. This might remove relationships created manually after the data was imported or introduce new relationships. However, if you want to do this, select the option.

   • Under Background Data, select or clear Allow data previews to download in the background.
     
     The default behavior is to download data previews in the background. Clear this option if you can want to see all the data right away.

See Also

Power Query for Excel Help

Manage queries in Excel