How to Identify the Source Table of Appended Rows in Power Query
Question details
The user needs to know which original source table each row belongs to after appending multiple tables in Power Query.
- Product
- Power Query / Power BI
- Device & OS
- not provided
- Scenario
- Appending two or more tables together for data analysis, but needing to retain the origin table identity for future data separation or filtering.
- Observed behavior
- The tables are successfully appended, but the resulting combined dataset lacks a column indicating whether a row originated from the first table, second table, or another source.
Ensure you have access to the original unappended queries in your Power Query Editor, as you will need to modify the individual tables before they are combined.
Add a Custom Source Column Before Appending Tables
The simplest and most reliable method is to manually add a custom column to each table containing its name before performing the append operation.
By tagging each row with its respective table name before the merge, Power Query will seamlessly align the custom columns during the append process, giving you a unified dataset with a clear source identifier.
Launch Power Query Editor in Excel or Power BI and select your first table query from the Queries pane on the left.
Navigate to the 'Add Column' tab on the top ribbon and click on 'Custom Column'.
Name the new column 'Source Table'. In the formula box, enter the table's name enclosed in quotation marks (e.g., "Sales_2022"), then click 'OK'.
Select your second table from the Queries pane and repeat the previous step, ensuring the column name 'Source Table' is typed exactly the same, but change the formula to reflect the second table's name (e.g., "Sales_2023").
Select the first table again, go to the 'Home' tab, click 'Append Queries' (or 'Append Queries as New'), select your second table, and click 'OK'. The resulting table will now display the 'Source Table' column.
Use the Workbook Import Method to Automatically Retain Sheet Names
If you are importing multiple tables or sheets from a single Excel workbook, you can extract the source names automatically during the initial data fetch.
Merge Multiple Worksheets and Keep Source Names Automatically in WPS Office
If you are struggling with complex Power Query formulas just to append tables, WPS Spreadsheets offers a built-in 'Merge Worksheets' feature. It easily combines multiple sheets into one and automatically adds a column to identify the original source worksheet without writing any code.
- 1. Open your Workbook: Open your workbook containing multiple tables or sheets in WPS Spreadsheets.
- 2. Access the Merge Tool: Navigate to the 'Data' tab and click on the 'Merge Worksheets' button.
- 3. Select Sheets to Combine: In the popup dialog, choose 'Merge multiple worksheets into a worksheet' and check the boxes for the sheets you want to append.
- 4. Enable Source Tracking: Ensure the option to include the worksheet name in the merged data is checked, then click 'Merge' to generate your consolidated data.
Frequently Asked Questions
Can I add the source table name after appending the tables?
No, once the tables are appended without a source column, the data is mixed, and Power Query cannot inherently trace back the original table for each row. You must add the identifier column before or during the append step.
Why isn't the 'Table Name' column appearing when I import multiple sheets?
If you select individual sheets from the Navigator window during import, Power Query creates separate queries for each without retaining the parent file or sheet name. To keep the name, you need to import the entire workbook at the root level and expand the 'Data' column manually.
Does this method work for appending more than two tables?
Yes. You can add a custom 'Source' column to as many tables as you need. When you use the 'Append Queries' feature, switch the toggle to 'Three or more tables', and add all your prepared tables to the list to append them simultaneously.
What happens if my custom source columns have different names in each table?
Power Query relies on exact column names to align data during an append. If you name one custom column 'Source' and another 'SourceTable', the appended query will create two separate columns with null values for the unmatched rows. You must ensure the custom column name is identical across all tables.
