Power Query Problems

How to Use Power Query with Data in the Same Excel Workbook

Rana Garcia Oct 16, 2026 869 views

Question details

The user wants to pull source data from one worksheet into Power Query within the same Excel workbook without needing to connect to an external file.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Retrieving and transforming local spreadsheet data within a single workbook using the Power Query editor.
Observed behavior: The user needs a method to access internal tables directly within the Power Query editor using the Excel.CurrentWorkbook() function instead of importing from an external source.

Before you start

Ensure your source data is organized into clear rows and columns without blank headers, as it must be converted into an official Excel Table before it can be read by Power Query.

Solution 1Recommended

Format as an Excel Table and use Excel.CurrentWorkbook()

The most direct way to use data from the same workbook is to format it as a Table and access it using the built-in current workbook function in Power Query.

Power Query can seamlessly retrieve source data from another worksheet in your current file, provided the range is defined as an Excel Table. You do not need to save the data in a separate file to establish the connection.

Convert Source Data to a Table

Go to the worksheet containing your source data, select the entire data range, and press Ctrl+T (or go to Insert > Table) to format it as an Excel Table. Ensure 'My table has headers' is checked.

Open a Blank Query

Navigate to the Data tab on the Excel ribbon, click on Get Data (or Get & Transform Data), select From Other Sources, and click on Blank Query to launch the Power Query Editor.

Use the CurrentWorkbook Function

In the formula bar of the Power Query Editor, type = Excel.CurrentWorkbook() exactly as shown (it is case-sensitive) and press Enter. This will display a list of all tables and named ranges in your current workbook.

Expand Your Table Data

Locate your newly created source table in the list. Click on the 'Table' link next to its name to expand the data columns and load them into your query for further transformation.

Format as an Excel Table and use Excel.CurrentWorkbook()

Direct Table Import Alternative: Instead of a blank query, you can also select any cell inside your new Excel Table and simply click 'From Table/Range' on the Data tab to load it directly into Power Query.

Solution 2

Reference an Existing Query in the Same Workbook

If you are creating a new query based on data produced by an existing Power Query, you can chain them by referencing the original query directly.

Free Microsoft Office alternative

Process Your Spreadsheet Data Easily with WPS Office

While advanced Power Query scripting (M language) is specific to Microsoft Excel, WPS Office provides a lightweight, highly compatible, and free alternative for comprehensive data analysis. Enjoy built-in PivotTables, advanced filtering, and seamless handling of all your Excel workbooks.

1. Download and Install: Visit the official WPS Office website to download the free suite and install it on your device.
2. Open Your Excel Files: Launch WPS Spreadsheet and open your existing .xlsx files directly; your formatting and data remain perfectly intact.
3. Analyze Data: Utilize the comprehensive Data tab to access PivotTables, advanced filters, and calculation tools for your everyday analytical needs.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) file formats.Built-in advanced data analysis tools including PivotTables and robust functions.Lightweight design ensures fast loading and smooth processing of large datasets.Familiar user interface makes switching from Microsoft Office effortless.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't Power Query recognize my worksheet data?

Power Query requires local workbook data to be officially formatted as an Excel Table or a Named Range. If your data is just raw text in cells, Power Query cannot see it. Select your data and press Ctrl+T to convert it into a table first.

Can I output the Power Query result to the same workbook?

Yes. Once you have finished transforming your data, click 'Close & Load To...' in the Power Query Editor and choose to output the result as a Table on a new or existing worksheet within the current workbook.

What happens to the query if I rename the source table?

If you change the source table's name in Excel, the Power Query connection will break, resulting in an error. You must open the Power Query Editor, go to the Advanced Editor or the 'Source' applied step, and update the name to match the new table name.

Does using Excel.CurrentWorkbook() affect file performance?

Generally, it performs well for standard tasks. However, if your query references the entire workbook and outputs back to the same workbook without strict filtering, it can create a circular reference or slow down refresh times. Always ensure you are only expanding the specific tables you need.

Excel & Data Problems