How to Automatically Include New Columns in Power Query
Question details
The user needs newly added columns from source tables to appear automatically when combining multiple files or tables, avoiding the need to manually select them during the expansion step.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining data from multiple tables or a folder using Power Query.
- Observed behavior
- New source columns do not appear in the combined table until they are manually selected in the static 'Expanded Data' step.
Before editing your query, ensure you have a basic understanding of the Power Query Advanced Editor and back up your original workbook to prevent accidental data loss during M code modifications.
Use a Dynamic Expansion List in the Advanced Editor
Modify the M code to dynamically check and include all distinct column names from the source tables during the expansion step.
By default, Power Query hardcodes the column names present at the exact moment you expand a table. To force it to recognize new columns automatically, you must replace the static array of column names with a dynamic list function.
Navigate to the View tab in the Power Query Editor ribbon and click on 'Advanced Editor' to reveal the underlying M code for your query.
Locate the step immediately before your 'Expanded Data' step (usually a filtering or custom column step). Rename this step to 'XXX' to make the subsequent code easier to write. Ensure the line ends with a comma.
Ensure that the column containing your nested table objects is named 'Content'. If it is currently named 'Custom.Data' or something else, rename it to 'Content' in the step prior to expansion.
Replace your static Table.ExpandTableColumn step with the following dynamic formula: Table.ExpandTableColumn(XXX, "Content", List.Union(List.Transform(XXX[Content], each Table.ColumnNames(_))))
Click 'Done' at the bottom of the Advanced Editor. Power Query will now dynamically aggregate and display all columns from your source tables, including newly added ones.
Looking for a Simpler Way to Manage Spreadsheet Data?
If you are struggling with complex Power Query formulas and M code in Excel, consider trying WPS Office. It provides a lightweight, highly compatible, and user-friendly spreadsheet environment that seamlessly handles complex data consolidation without steep learning curves.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your computer.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open your existing Excel (.xlsx) file directly, with full format retention.
- 3. Process Your Data: Utilize the built-in Data tab tools in WPS Spreadsheet to consolidate, filter, and manage your tables effortlessly.
Frequently Asked Questions
Why does Power Query hardcode column names during expansion?
By default, when you click the expand button on a table column, Power Query generates static M code based on the columns present at that exact moment. This improves processing performance but prevents new columns from being included automatically.
What happens if a source file is missing a column after applying the dynamic code?
The List.Union function aggregates all distinct column names across all source tables. If a specific table is missing a column, Power Query will seamlessly populate those rows with null values for that column.
Does this dynamic expansion method work when combining data from a folder?
Yes. As long as you correctly identify the step containing the list of files and ensure the column holding the table objects is properly referenced, the List.Union method works flawlessly for folder queries.
Why am I getting a syntax error after pasting the dynamic M code?
Errors usually occur due to missing commas at the end of the previous step, incorrect step name references (e.g., mismatching your 'XXX' placeholder with the actual previous step name), or the table column not being explicitly named 'Content'.
