How to Automatically Include New Columns in Power Query Appended Tables
Question details
The user needs Power Query to automatically pull in newly added columns from source files when expanding appended tables.
- Product
- Excel / Power Query
- Device & OS
- not provided
- Scenario
- Appending multiple data tables and expanding them in Power Query when the source file schemas frequently add new columns.
- Observed behavior
- Queries explicitly listing columns during the table expansion step fail to include any new columns added to the source files.
Before editing your M code, duplicate your query to safely test the changes and ensure your source files do not contain duplicate column headers.
Replace Fixed Expansion List with a Dynamic Column List
Modify the M code in the Advanced Editor to dynamically evaluate and extract all column names from the appended source tables.
When you expand a table in Power Query via the standard user interface, the system hardcodes the currently visible column names. To make this resilient to future schema changes, you can replace this static list with a dynamic formula using List.Union and Table.ColumnNames.
Navigate to the 'Home' tab in the Power Query Editor ribbon and click on 'Advanced Editor'.
Find the line of M code corresponding to your table expansion step, usually starting with #"Expanded Custom.Data" = Table.ExpandTableColumn.
Look for the hardcoded list of column names at the end of the ExpandTableColumn function, formatted like {"ColumnA", "ColumnB"}.
Replace the fixed list with the dynamic list formula: List.Union(List.Transform(#"Removed Other Columns"[Custom.Data], Table.ColumnNames)). Ensure the previous step reference (#"Removed Other Columns") matches your actual query history exactly.
Click 'Done' to close the Advanced Editor, verify that the new columns appear in the data preview, and click 'Close & Load' to update your workbook.
Experience Fast and Reliable Data Processing with WPS Office
Complex data transformations often require heavy troubleshooting in M-code. For an easier, lightweight, and highly compatible spreadsheet experience, try WPS Office. It provides powerful data manipulation features without the overhead of heavy backend query languages.
Frequently Asked Questions
Why are my new source file columns not appearing after a Power Query refresh?
By default, Power Query records the exact column names present at the moment you click 'Expand Table'. If new columns are added to the source later, the static M code ignores them until you update the query to use dynamic column expansion.
Can I dynamically expand columns without using the Advanced Editor?
Currently, the standard Power Query user interface does not offer a toggle for dynamic column expansion. You must manually modify the M code using the Advanced Editor to replace the static list with Table.ColumnNames.
What happens if a dynamically expanded column shares a name with an existing table column?
Power Query requires column names to be unique within a table. If a newly expanded column has the same name as an existing column outside the expansion step, the query will return an error during refresh. Ensure your column headers remain distinct.
