How to Merge Dynamic Columns and Keep Others in Power Query
Question details
The user needs to dynamically combine columns with names starting with the word "Labels" in Power Query, ensuring that all other unselected columns in the dataset are kept intact.
- Product
- Excel (Power Query)
- Device & OS
- not provided
- Scenario
- Performing data transformation where the number of label columns might change with each data refresh, requiring a dynamic approach rather than hardcoding column names.
- Observed behavior
- Standard merging often results in referencing hardcoded column names or dropping unselected columns if the user manually isolates the target columns before merging.
Ensure your source data is formatted as an Excel Table (e.g., named "Export_Data") and loaded into the Power Query Editor so you can safely edit the M code.
Use Table.CombineColumns with a Dynamic List in Advanced Editor
Use Power Query M code to dynamically extract a list of columns starting with "Labels" and pass that list directly to the Table.CombineColumns function to preserve the rest of the table.
The key to keeping all other columns is to avoid highlighting and isolating the target columns before the merge step. Instead, you can dynamically generate the list of target column names and pass it directly to the combine function within the context of the original source table.
In Power Query Editor, go to the Home tab and click on "Advanced Editor" to view your query's underlying M code.
Ensure your source table is defined. Then, retrieve all column names dynamically by adding this step: `SourceNames = Table.ColumnNames(Source)`.
Create a filtered list of columns that start with "Labels" using List.Select: `LabelColumns = List.Select(SourceNames, each Text.StartsWith(_, "Labels"))`.
Merge the filtered columns while preserving the rest by passing the dynamic list directly. Add conditional logic to avoid errors if there's only one column: `Result = if List.Count(LabelColumns) < 2 then Source else Table.CombineColumns(Source, LabelColumns, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "MERGED LABELS")`.
Ensure the "in" statement at the end of your script points to `Result`, then click "Done" to apply the changes.
Looking for a Faster, Free Alternative to Excel?
While Power Query in Microsoft Excel is great for complex scripting, everyday data merging tasks can be completed much faster using standard functions. WPS Office offers a highly compatible, lightweight, and free alternative for all your spreadsheet needs.
- 1. Download WPS Office: Go to the official WPS website and install the free office suite.
- 2. Open your spreadsheet: Launch WPS Spreadsheet and open your existing Excel file seamlessly.
- 3. Use built-in functions: Utilize functions like CONCATENATE or TEXTJOIN to merge your columns instantly without writing scripts.
Frequently Asked Questions
Why are my other columns disappearing when I merge in Power Query?
This usually happens if you highlight specific columns, right-click, and select "Remove Other Columns" before merging, or if your M code uses Table.SelectColumns prior to the merge step. Applying the merge directly to the source table preserves unselected columns.
How can I change the delimiter when combining dynamic columns?
In your M code, modify the Combiner.CombineTextByDelimiter function. For example, change (" ", QuoteStyle.None) to (", ", QuoteStyle.None) to separate your merged text with a comma and a space instead of just a space.
Will this M code break if there is only one "Labels" column in the future?
No, the provided code includes an 'if' statement checking if the count of LabelColumns is less than 2. If that condition is true, it returns the original table without attempting to merge, preventing errors.
Can I search for columns ending with specific text instead of starting with it?
Yes. In the List.Select function step, you can simply replace Text.StartsWith(_, "Labels") with Text.EndsWith(_, "YourText") to dynamically filter columns based on their suffixes.
