Power Query: Close Section and Queries Pane in Home Tab

After spending time transforming data—removing duplicates, changing data types, splitting columns, or merging tables—you need to decide how to bring the cleaned data back into Excel or Power BI. This is where the Close section comes into play.

It is located on the far right side of the Home Tab inside the Power Query Editor. The Close section provides three main options:

Power Query Editor interface displaying various options for data transformation, including closing and loading queries, managing columns, and data sourcing.

1. Close & Load

This option loads your transformed query directly into Excel. By default, the data will appear as a new worksheet table, ready for analysis.

• ✅ Ideal when you want to immediately use the data in PivotTables, charts, or further Excel formulas.

• ⚠️ Be cautious when dealing with very large datasets, as loading everything into Excel might slow down performance.

In the Query Editor, the process of loading data with selected modifications is showcased as users are prompted to save changes and specify the destination in Excel.

2. Close & Load To…

This is the more flexible version. Instead of simply loading the data, it allows you to choose:

• Load as a table in a worksheet

• Load as a PivotTable or PivotChart

• Load only as a connection (without placing it into a sheet)

• Load into the Data Model for advanced reporting and DAX calculations

This option is especially powerful if you’re working with multiple queries that act as intermediate steps. You don’t always need every query visible in your workbook—sometimes just having a connection is enough.

3. Close

This option simply closes the Power Query Editor without loading the data into Excel. Don’t worry, your transformations are not lost. The query itself remains saved in your workbook, and you can reopen it later for further work.

Pro Tip: If you are experimenting or testing transformations, use Close. Once you are satisfied with your results, switch to Close & Load or Close & Load To… to finalize the process.

Why the Close Section is Crucial

Many beginners underestimate the importance of the Close section. However, your choice here directly affects performance, file size, and even the maintainability of your Excel model.

• Choosing Close & Load To (Connection only) can drastically improve performance when working with staging queries.

• Using Data Model loading enables advanced Power Pivot analysis.

• Simply using Close helps keep your workbook uncluttered during early transformation stages.

Think of the Close section as the “final step” of your data journey in Power Query. Without properly using it, you might end up with unnecessary tables, duplicated data, or slow Excel performance.

Data transformation and management in Power Query with options for creating new queries, accessing various data sources, and applying multiple data processing steps.

Queries Pane: Your Control Center for Queries

While the Close section decides how data exits Power Query, the Queries pane is where you manage everything inside the editor. It is located on the left-hand side of the Power Query Editor and displays all queries in your workbook.

If you are working with just one query, you may not pay much attention to it. But once your workbook contains 5, 10, or even 50 queries, the Queries pane becomes your best friend.

Key Actions in the Queries Pane

1. Rename Queries always give meaningful names like SalesData_2024 or CustomerList instead of leaving them as Query1, Query2. Clear naming prevents confusion later.

2. Duplicate QueriesCreates an independent copy of an existing query. Perfect for trying out alternative transformations without touching the original data.

3. Reference QueriesCreates a new query that is dependent on another query. Any changes in the original will automatically affect the referenced query. This is essential for building efficient data models.

4. Delete QueriesRemoves unnecessary or test queries, keeping your workbook clean.

5. Enable/Disable LoadRight-click on any query and decide whether it should be loaded back into Excel. Queries used only for intermediate steps can remain as connections, which improves efficiency.

6. Organize QueriesYou can drag and drop queries to keep them grouped logically. For example, keep all “Source Queries” at the top and “Final Queries” at the bottom.

Practical Example: Combining Close Section and Queries Pane

Let’s say you are preparing a sales report:

1. You import raw sales data from an external CSV file (Query1).

2. You create another query that cleans this data—removing duplicates and fixing data types (Sales_Cleaned).

3. You then reference this query to build region-specific tables (Sales_Region).

4. Some queries (like intermediate steps) are set to connection only using Close & Load To….

5. Finally, your finished report queries are Close & Load into Excel as tables for analysis.

By using the Queries pane for organization and the Close section for final loading, you keep your workbook both efficient and easy to maintain.

Best Practices to Keep in Mind

• Use Clear Names: Rename every query as soon as you create it.

• Leverage “Connection Only”: Not all queries need to be loaded—save resources by loading only final outputs.

• Experiment with Duplicates: If you’re unsure about a transformation, duplicate the query and test freely.

• Document Steps: Consider adding notes or comments to remember why you applied certain transformations.

• Group Queries: If you have many queries, use folders (Groups) to categorize them.

Final Thoughts

The Close section and Queries pane may seem simple at first glance, but they are vital to working efficiently in Power Query. The Close section ensures that your data is loaded correctly and in the right form, while the Queries pane keeps everything organized and manageable.

By mastering these two areas, you save time, avoid unnecessary duplication, and build models that are scalable and easy to maintain. Whether you’re an Excel enthusiast or a Power BI professional, these tools will make your data workflows smoother and far more effective.