Column Standardizer – Power Query Function

Have you worked with an Excel or CSV file that changes slightly their column names? Power Query is very susceptible to any change, especially using functions such as Table.ReorderColumns or Table.SelectRows. A single renamed header can break your entire data refresh.

The Problem

Consider a monthly sales table where column headers vary slightly each time the file is exported:

• Date.Period, Date.Time, Sales.Amount, Profit_Amount

These headers contain unnecessary characters and inconsistent formatting. When you try to append monthly files together, Power Query fails because it expects exactly the same column names every time.

The Solution: ColumnStandardizer

The ColumnStandardizer function normalises column names from a source table against a predefined list of standard names. It uses text similarity matching with a configurable scoring threshold to identify the best match for each column, regardless of minor differences in punctuation, spacing, or capitalisation.

Standard column names defined

1. Date Period
2. Date Time
3. Sales Amount
4. Profit Amount

Implementation Steps

1. Define your standard column names as a list.
2. Extract the actual column names from your source table.
3. Pass both lists to the ColumnStandardizer function — it returns a renamed table.
4. For handling unexpected columns, combine ColumnStandardizer with Table.SelectColumns to drop anything that doesn't match.

Resources

The full implementation and a sample Power BI file are available on GitHub:

• github.com/acanepas/ColumnStandardizer