How to Update the Source Range Used by Power Query in Excel
Question details
The user needs to update the source range in Power Query because it is not importing all the newly pasted rows from the worksheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Pasting new data into a worksheet and refreshing a Power Query connection to import the updated dataset.
- Observed behavior
- Power Query only imports a portion of the data (e.g., 489 out of 552 rows) because the query source range or table definition has not expanded to include the new rows.
Ensure that there are no completely blank rows interrupting your dataset, as this can cause Excel tables or named ranges to misinterpret the end of your contiguous data range.
Resize the Excel Table or Update the Named Range
If your Power Query source is an Excel Table or a Named Range, you must ensure it encompasses all the newly added rows before refreshing.
Power Query typically relies on a structured reference (like an Excel Table) or a specific Named Range to know exactly where to pull data from. If data is pasted outside the boundaries of this defined area, Power Query will ignore it.
Open the Power Query Editor, select your query, and look at the 'Source' step in the Applied Steps pane to determine if it references a Table or a Named Range.
If the source is a table, return to your Excel worksheet, click anywhere inside the table, go to the 'Table Design' tab, and click 'Resize Table'. Select the new range that includes all newly pasted rows.
If the source is a named range, go to the 'Formulas' tab and click 'Name Manager'. Select your named range, update the 'Refers to' field at the bottom to include the new rows, and click the checkmark to save.
Go to the 'Data' tab on the Excel ribbon and click 'Refresh All' to import the updated data range into Power Query.
Force Excel to Update Worksheet Dimensions
Use this method if Power Query is reading directly from the worksheet and is stuck on stale worksheet XML dimensions.
Try WPS Office for Seamless Spreadsheet Management
While Power Query is a feature specific to Microsoft Excel, WPS Office provides a free, lightweight, and highly compatible alternative for everyday spreadsheet tasks. Enjoy a familiar interface and robust data analysis tools without the hefty subscription fees.
- 1. Download and Install: Visit the official WPS website to download the free installation package for your operating system.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and easily open any existing .xlsx or .csv file to continue your work without formatting loss.
- 3. Analyze Data: Use built-in data tools like Pivot Tables, filters, and advanced formulas to process your datasets efficiently.
Frequently Asked Questions
Why does Power Query only import a partial dataset?
This usually happens because the data source is defined as a specific range or Excel Table that hasn't automatically expanded to include the newly pasted rows. Power Query strictly obeys the boundaries of the source defined in its first step.
How do I make my Excel Table expand automatically for Power Query?
Ensure you paste new data directly adjacent to the last row of the existing table. If there is even one blank row between the table and the new data, the table will not expand automatically to absorb the new information.
What is the Name Manager and how does it affect Power Query?
The Name Manager stores custom defined ranges in Excel. If Power Query uses a named range as its source, it will only import the exact cells defined there. You must manually update the range in the Name Manager via the Formulas tab if your dataset grows.
Does clicking 'Refresh' automatically update the source range?
No. Refreshing the query only pulls data from the currently defined source range. If the range itself hasn't been updated or resized in your Excel worksheet, refreshing will not bring in rows that fall outside that defined area.
