Power Query does not provide a worksheet-style command to insert a row directly into a query result. To add a record that survives refresh, add it to the source data and refresh, or create a small table for the extra record and append it to the query.
What “Insert a Row” Means in Power Query
In Excel, inserting a worksheet row means making space between existing cells. Power Query works differently: it reads data from a source, applies a sequence of transformations, and loads the resulting table to a worksheet or another destination. The query output is generated from those instructions; it is not an independent range for you to edit freely.
That difference matters when a row is added. You can type into the worksheet table that Power Query loaded, but that does not add the entry to the query’s source or transformation steps. On refresh, the output is rebuilt from the query, so a manual edit may be overwritten, moved, or left outside the refreshed table. The dependable method is to make the new record part of the query’s inputs or add it through a query transformation.
First decide which result you need:
- A new source record: add it to the underlying Excel table or source file, then refresh.
- A supplemental record: store it in a separate table and append that table to the main query.
- A row at a specific position: insert it into the query with a transformation, taking care to define the order.
- A visual blank separator: consider whether a blank row belongs in the data at all; it is usually better to format the report rather than interrupt a clean dataset.
Add the Row to the Source Excel Table
If the row is a genuine new record in the data being imported, changing the source is usually the simplest and clearest solution. For a source stored in the same workbook, make sure the data is an Excel table rather than a fixed block of cells. Select a cell in the data and choose Insert > Table, or use the existing table if it already has filter arrows in its headers. On Windows, Ctrl+T opens the Create Table dialog; on Mac, use Command+T.
Add the record as a new table row, keeping values under the correct column headings. You can type in the row immediately below the table, which Excel will commonly expand to include, or use Tab from the last cell of the table’s last row to create a new row. Then refresh the query: choose Data > Refresh All, or right-click the query output and choose Refresh. The query reads the updated source table and applies the same transformations to the added record.
For example, suppose a query reads a table named Sales with columns Date, Region, and Amount. If a new sale belongs in the source, add its date, region, and amount to Sales. Do not type the sale underneath the loaded query result and expect it to become part of Sales automatically: the source table and the query output table are separate objects, even if they appear on the same worksheet.
Use a Separate Table and Append It
Sometimes you should not alter the original source. You may need to add a manual correction, a one-off adjustment, or records maintained by another person. Put those entries in a separate Excel table with compatible column headings, load that table into Power Query, and append it to the main query. This keeps the extra records visible and makes the process repeatable.
- Create a small Excel table for the additional records. Use the same column names as the main dataset wherever the fields have the same meaning.
- Select a cell in that table and choose Data > From Table/Range to open it in Power Query. In the Power Query Editor, select Home > Close & Load To if you want to control how the query is loaded; the supplemental query can also be set to connection only.
- Open the main query by choosing Data > Queries & Connections and double-clicking it, or by editing it from the Power Query Editor.
- In the editor, choose Home > Append Queries. Select the supplemental query as the table to append, then confirm. Choose Append Queries as New instead if you want to preserve the original main query and create a separate combined result.
- Check the preview for the new records and verify the column types. Choose Home > Close & Load to load the result.
Append adds records vertically; it does not insert cells into the worksheet or match rows by a key. Power Query aligns appended fields by column name, not by their left-to-right position. If one table has a field that the other does not, the combined result can contain null values for that field in the rows from the table where it is absent. Rename columns and confirm their meanings before appending so that similarly named fields really represent the same information.
Insert a Row at a Particular Position in a Query
If the record must appear at a specific position in the query result, a Power Query transformation can insert it. The Power Query M language includes the Table.InsertRows function. In the Power Query Editor, you can add a step in the formula bar or edit the query in Home > Advanced Editor. The following is an illustrative step for a query whose previous step is named Changed Type and whose columns are ID and Name:
= Table.InsertRows(#"Changed Type", 2, {[ID = 999, Name = "Manual adjustment"]})
In this example, the record is inserted at position 2, which is the third position because row positions start at zero. The record fields must match the query’s column names, and the values must be compatible with their data types. Replace Changed Type with the actual name of the preceding step in your query, and replace the example fields and values with your own. If the table has other columns, account for them as well; otherwise, the inserted record may not have the values you expect or the step may produce an error.
This approach is useful for a small, deliberate transformation, but it is less convenient for entries that change frequently. A value embedded in an M step is harder for a colleague to update than a value in a clearly labeled Excel table. Also consider whether the row position is stable. If the source order can change between refreshes, sort the data by a reliable field before inserting the row; otherwise, position 2 may be between different records after a refresh.
Adding a Blank Row Is Usually a Different Problem
People often ask how to insert a blank row between Power Query results to make a report easier to read. A blank record is possible as a transformation, but it can complicate later steps and downstream analysis. Sorting, filtering, grouping, data-type conversion, and calculations may behave differently when a row has null values. A blank row can also make it harder to identify the end of a contiguous dataset or to create a PivotTable from the result.
For a polished report, keep the query output as a continuous table and use Excel’s formatting features instead. For example, apply banded rows through the table design options, add borders, or place explanatory text in a separate cell outside the data table. If a blank record is genuinely part of the required output, define exactly which columns should be null and where the row should appear, then test the result after refreshing and sorting.
Refresh Behavior and Row Order
Refresh runs the query again; it does not preserve arbitrary edits made to the loaded result as if they were source records. This is why a manual row typed into the output is not a reliable way to maintain extra data. Keep editable records in a source table, a separate supplemental table, or a documented query step instead.
Row order also needs attention. A source may appear in a particular order in the preview, but if the query does not explicitly sort the result, that order should not be treated as a permanent rule. If a new record must appear before or after a particular item, use a meaningful sort field, such as a date, sequence number, or category plus sequence. Then confirm the loaded output after refresh. A sort step and an insert step serve different purposes: sorting orders existing records by a rule, while insertion creates an additional record.
Before changing a query used by other workbooks or reports, check its load destination and dependencies. A query may feed another query, a PivotTable, or a Data Model. Adding a record can change totals, row counts, and downstream results. Refresh the dependent outputs and confirm that the new row has not introduced an unintended null, text-versus-number mismatch, or duplicate.
Which Method Should You Choose?
- Use the source table when the added row is part of the real source data and should be maintained alongside the other records.
- Append a supplemental table when manual additions need to remain separate, easy to review, or independently maintained.
- Use an M insertion step when the row is a fixed part of the transformation and its position is intentional and stable.
- Avoid inserting a worksheet row into the output when you expect the record to survive refresh. It changes the displayed sheet, not the query’s input.
- Avoid blank rows in a data table when the goal is only visual spacing; format the output or build a separate presentation layer instead.
For most users, adding to the source or appending a supplemental table is easier to maintain than editing M code. M is appropriate when the transformation itself should generate the record, but document the reason for the step so that the next person understands why the query contains a manually specified row.
Common Problems to Check
- The new source record does not appear: confirm that it is inside the source Excel table, refresh the query, and check any filters in the Applied Steps list. A filter may be excluding it.
- The appended values land under unexpected fields: check the column names and meanings. Append matches fields by name, so inconsistent headings can create separate columns.
- The inserted step shows an error: check the previous step name, spelling and case of field names, and compatibility between each value and the column’s data type.
- The row appears in a different place after refresh: add or review a sort step, and do not rely on an unspecified source order.
- A manually typed output row disappears or sits outside the table: move the record into a source or supplemental table, then refresh the query.
Frequently Asked Questions
Can I insert a row directly in the Power Query Editor?
No worksheet-style Insert Row command is available for editing a query result in place. Add a record through the source, append another query, or create a row with a Power Query transformation such as Table.InsertRows.
Will a row typed into the query output stay after refresh?
Do not rely on it. The loaded table is produced by the query, and refresh rebuilds that result. Maintain additional records in the source or a supplemental table and include them in the query.
How do I add one row from another table?
Load the other table into Power Query, then choose Home > Append Queries in the editor and select it as the table to append. Check that matching columns have the same names and compatible data types.
Can Power Query add a blank row between records?
It can be made to output a row with null values, but that may interfere with filtering, sorting, or analysis. If you only want extra visual spacing, format the worksheet output rather than adding a blank data record.
Why does my inserted row move after I refresh?
The query may not define a stable order, or an earlier sort step may change the row positions. Sort by a reliable field and place the insertion step after the step that establishes the order you need.
Related
- Office Inventory Spreadsheet: Setup, Tracking, and Tips
- Retirement Savings Tracker Spreadsheet: Build Yours
- PowerPoint Slideshow Loop: How to Repeat It Continuously
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Undo Archive Emails in OutlookTo undo archiving in Outlook, find the message in the Archive folder, select it, and move it to…
- How to Add an Image in Excel: Step-by-StepTo add an image in Excel for Microsoft 365 on Windows, open the worksheet, select Insert > Pictures…
- Excel Print Gridlines: How to Show Them on Paper and PDFYes. In Excel for Microsoft 365 on Windows, select the worksheet, open Page Layout, and under Sheet Options…
- How to Make One Page Landscape in Word for MacTo make just one page landscape in Word for Mac, isolate it in its own section with a…
- Google Sheets Apps Script Editor: How to Open and UseTo open the Apps Script editor for a Google Sheet, open the spreadsheet in a desktop browser and…
- Excel Dropdown From Another Sheet: Setup and FixesYes. In Excel for Microsoft 365 on Windows, create a workbook-level named range for the cells on the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.