How to Append Multiple Web Tables into One Excel Power Query Table
Question details
The user needs to combine multiple data tables imported from a single web page into one consolidated table using Power Query.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing web data where the desired information is split across multiple tables on the same webpage, requiring consolidation for analysis.
- Observed behavior
- Power Query imports each web table as a separate query by default instead of a single consolidated output table.
Ensure you have the exact URL of the web page containing your data and verify that the tables you plan to append share identical or highly similar column structures.
Append Connection-Only Web Queries in Power Query
Import the web tables as connection-only queries first, then use the Append Queries feature to combine them into a single table before loading the data into Excel.
By setting the initial imports to 'Connection Only', you prevent Excel from creating unnecessary worksheets for each individual web table. You can then consolidate them purely within the Power Query Editor.
Go to the Data tab on the Excel ribbon, click 'Get Data', then select 'From Web'. Enter the URL of the webpage and click OK. In the Navigator pane, check 'Select multiple items' and select all the required tables.
Instead of clicking Load, click the dropdown arrow next to Load and select 'Load To...'. In the Import Data dialog box, choose 'Only Create Connection' and click OK. This prevents the individual tables from loading into your spreadsheet.
Double-click one of your new queries in the Queries & Connections pane on the right side of your Excel window to open the Power Query Editor.
In the Power Query Editor, go to the Home tab, click the 'Append Queries' dropdown, and select 'Append Queries as New'. Choose the 'Three or more tables' option, add all your web table queries from the Available tables list to the 'Tables to append' list, and click OK.
Review the newly appended query to ensure columns align correctly. Standardize column names and data types if necessary. Finally, click 'Close & Load' on the Home tab to load the combined output table into your Excel worksheet.
Try WPS Office for Seamless Data Management
While advanced Power Query appending is native to Microsoft Excel, WPS Office provides a powerful, lightweight, and highly compatible alternative for everyday spreadsheet tasks, data analysis, and basic data imports without the heavy subscription fees.
- 1. Download the software: Visit the official WPS Office website and click the Free Download button.
- 2. Install WPS Office: Run the downloaded installer and follow the simple on-screen instructions to complete the setup.
- 3. Open your spreadsheets: Launch WPS Spreadsheet and instantly open any existing .xlsx files to continue your data analysis.
Frequently Asked Questions
Why do my appended tables have null values in certain columns?
This occurs when the appended tables have different column header names. Power Query appends based on exact column names. Ensure all tables have identical headers before appending to avoid null values.
What is the difference between merging and appending queries?
Appending adds rows from one table to the bottom of another, similar to stacking them. Merging combines columns from multiple tables based on a matching identifier, similar to a VLOOKUP function in Excel.
Can I edit the individual connection-only queries later?
Yes. You can double-click any connection-only query in the Queries & Connections pane to open it in the Power Query Editor. Any transformations you apply to the base query will automatically flow through to your final appended table.
