'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Align Two Excel Tables and Insert Blank Rows Using Power Query
Question details
The user wants to compare two side-by-side Excel tables and align matching records based on specific columns while automatically inserting blank rows where data is missing.
- Product
- Excel 365
- Device & OS
- not provided
- Scenario
- Aligning two separate datasets with matching identifiers (like Plan and Elev) into a single unified view without using VBA macros.
- Observed behavior
- Needs an automated, repeatable way to merge tables and display blanks for non-matching records instead of manual alignment or complex nested formulas.
Ensure both of your datasets are formatted as official Excel Tables (press Ctrl + T) and that they share identically named columns for the identifiers you want to match, such as 'Plan' and 'Elev'.
Align Tables Using Power Query Full Outer Join
Using a Full Outer Join in Excel 365 Power Query allows you to merge both tables and automatically generate blank cells where records do not match, without writing any code.
A Full Outer Join combines all rows from both tables. When a match is found based on your specified columns, the data aligns on the same row. If a record exists in only one table, Power Query will output null (blank) values for the missing data from the other table.
Click anywhere inside your first table, go to the Data tab on the ribbon, and select 'From Table/Range'. Once the Power Query Editor opens, click 'Close & Load To...' and choose 'Only Create Connection'. Repeat this process for the second table.
In Excel, go to Data > Get Data > Combine Queries > Merge. This will open the Merge dialog box.
In the Merge window, select your first table from the top dropdown and your second table from the bottom dropdown. Click on the column headers (e.g., 'Plan' and 'Elev') in both previews to highlight the matching criteria. Hold CTRL to select multiple columns.
At the bottom of the Merge dialog, locate the 'Join Kind' dropdown menu. Select 'Full Outer (all rows from both)' and click OK.
In the resulting Power Query Editor window, click the expand icon (two diverging arrows) on the header of the newly merged column. Uncheck the columns you don't need and click OK. Finally, use the sort buttons on your key columns to group matching records together.
Click 'Close & Load' from the Home tab to output the newly aligned dataset into a fresh Excel worksheet.
Try WPS Office for Your Spreadsheet Needs
While Power Query is a specific feature of Excel 365, WPS Office provides a lightweight, free, and highly compatible alternative for standard data analysis, alignment, and formatting tasks without expensive subscription fees.
- 1. Download and Install: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Open Your Excel Files: Launch WPS Spreadsheets and open your existing .xlsx or .xls files directly. Your formatting and standard formulas will be preserved.
- 3. Analyze Data: Use powerful built-in functions like XLOOKUP, pivot tables, and conditional formatting to analyze and align your data effectively.
Frequently Asked Questions
Can I align two tables and insert blank rows using formulas instead of Power Query?
Yes, you can use advanced formulas combining IF, IFERROR, and XLOOKUP or INDEX/MATCH across multiple columns. However, Power Query is significantly faster and handles the insertion of missing blank rows automatically without needing complex nested logic.
Why did my Full Outer Join create duplicate rows?
Duplicates occur in a merge if your selected identifier columns (e.g., Plan and Elev) do not contain strictly unique combinations in your source tables. Ensure your tables have unique identifier keys before performing the join.
Will my merged table update if I add new data to the original tables?
Yes. One of the primary advantages of Power Query is that it retains your transformation steps. Simply add your new data to the original tables, then right-click your merged output table and click 'Refresh' to update the layout.
