Power Query Problems

How to Select Columns by Text Match and Fixed Names in Power Query

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to import CSV columns in Power Query by dynamically selecting certain columns based on a specific text phrase while maintaining other columns by their exact, fixed names.

Product: Excel Power Query
Device & OS: not provided
Scenario: Importing and transforming varying CSV data where some column names change but contain a identifiable text pattern, alongside static column names.
Observed behavior: The goal is to successfully filter the dynamic column names and combine them with fixed-name columns to retrieve the correct dataset structure.

Before you start

Ensure your source CSV files are accessible and clearly identify which column names are fixed and which text phrases you need to match dynamically.

Solution 1Recommended

Filter and Combine Column Names in Power Query

Extract all column headers as a list, filter for the required text, and merge it with your fixed columns.

Power Query (M language) allows you to manipulate column headers as lists. By generating a list of all current columns, you can dynamically filter for those containing your target phrase and append your fixed column names to create a unified selection.

Extract Column Names

After loading your CSV file into the Power Query Editor, create a Custom Step and use the Table.ColumnNames function to generate a list of all column headers from your source step.

Filter by Text Match

Use the List.Select function combined with Text.Contains to isolate the columns that include your specific phrase from the generated list.

Combine with Fixed Names

Use the List.Combine function to merge your manually typed list of fixed column names (e.g., {"ID", "Date"}) with the dynamically filtered list.

Apply Column Selection

Wrap your original table source in the Table.SelectColumns function, using your combined list as the second argument to finalize the dynamic column selection.

Dynamic Resilience: By generating lists dynamically, your query will not break if future CSV exports add new columns that match your text phrase.

Solution 2

Prepare Sanitized Sample Files for Troubleshooting

If you require community support for complex transformations, prepare a sanitized workbook to demonstrate the expected output.

Free Microsoft Office alternative

Need a Lightweight Tool for Data Analysis? Try WPS Office

If you want a fast, reliable, and highly compatible tool for managing CSV files and complex datasets without expensive subscriptions, WPS Office is an excellent choice. It provides powerful data processing capabilities in a familiar interface.

Fully compatible with Microsoft Excel formats, including .xlsx and .csv files.Lightweight architecture ensures fast loading and smooth processing of large datasets.Familiar user interface makes data sorting, filtering, and analysis instantly intuitive.Completely free core spreadsheet functions for everyday personal and professional use.

Frequently Asked Questions

Can I use wildcards to select columns in Power Query?

Power Query does not use standard wildcard characters like asterisks (*) for column selection. Instead, you use text functions like Text.Contains, Text.StartsWith, or Text.EndsWith inside a List.Select function to dynamically match column headers.

Why does my query break when new columns are added to the CSV file?

If you hardcode all column names in the Table.SelectColumns step, any missing or renamed columns will throw an error. Using dynamic list functions ensures your query adapts to structural changes as long as the text matching criteria are met.

How do I ignore case sensitivity when matching column text?

You can use the Comparer.OrdinalIgnoreCase argument inside the Text.Contains function to ensure your column selection works properly regardless of uppercase or lowercase letters in the header.

What happens if no columns match my specific text phrase?

If no columns match the criteria, List.Select simply returns an empty list. When this is combined with your fixed columns, Table.SelectColumns will return only the fixed columns without throwing an error, provided those fixed columns exist in the source.

Excel & Data Problems