KajoData / Career & business
Power Query column names: rules, mappings and collisions
I transform headings in M, identify naming collisions and separate formatting rules from decisions about what a column means.

January’s export uses Sales_Total, February’s uses SALES_TOTAL, and March’s uses Sales Total . I want one repeatable import instead of repairing later steps every month. Power Query can transform column names with Table.TransformColumnNames. First, however, I need to decide which differences I am allowed to remove.
I separate formatting from meaning. Changing letter case is a text operation. Deciding that Amount and Revenue represent the same measure requires a decision about the data. This guide shows a limited, explicit transformation and the points where a mapping table or a schema check becomes necessary.
Choose the target convention first
For the exercise I use English technical names in lowercase, with spaces between words. I remove control characters and leading or trailing whitespace. Underscores and ordinary hyphens become spaces. Other characters remain because I have not established a reason to delete them.
This is deliberately a limited convention. It does not promise complete snake_case conversion, transliteration of Polish characters or recognition of the word boundary in productname. It also does not collapse every sequence of internal spaces. Stating those limits prevents readers from attributing much broader capabilities to a short function.
Before implementation, I ask whether names support later calculations or serve mainly as display labels. A stable technical convention may suit a model, while a report needs localised names and carefully capitalised abbreviations. One naming convention does not have to perform both jobs at the same time.
Inspect the incoming schema
I write down the input headings and their intended counterparts. This small step makes it possible to assess the transformation without deciphering a long expression. The following synthetic example uses visible code formatting to show the spaces around the first heading:
| Incoming name | Name after the agreed change |
|---|---|
Order_ID |
order id |
SALES_TOTAL |
sales total |
customer-id |
customer id |
productname |
productname |
The last row is intentional. None of the agreed operations splits joined words. Obtaining product name would require another rule or an explicit mapping. Similarly, Customer Number and Customer ID might need the same target name, but similar wording does not establish equivalent meaning in the source systems.
TransformColumnNames operates on headings
The Microsoft documentation describes a table and a name-generating function as its inputs. The result is a table with transformed column names. The function does not clean cell values or change the columns’ data types. Those are separate preparation operations.
The simplest version lowercases the names:
= Table.TransformColumnNames(#"Previous Step", Text.Lower)
Previous Step must be the actual name of the preceding step. If yours has a different name, reference that name instead of copying this placeholder literally. The example shows a single step in the formula bar. In the Advanced Editor it belongs inside a larger let expression.
M function names remain English in the Polish interface as well. Table.TransformColumnNames is not translated into a Polish worksheet function. Power Query expressions and Excel cell formulas are different languages, even when both appear in the same workbook. Keeping that distinction clear avoids needless errors when adapting examples.
Read the expression from the inside out
For the chosen convention I combine several operations:
= Table.TransformColumnNames(
#"Previous Step",
each Text.Replace(
Text.Replace(
Text.Lower(Text.Trim(Text.Clean(_)), "en-US"),
"_", " "
),
"-", " "
)
)
The underscore represents the current name passed into the function written with the each shorthand. I read the innermost expression first, then the surrounding calls. Cleaning is followed by trimming, lowercasing and the two separator replacements. Reading it this way makes the sequence much easier to explain.
For Order_ID, trimming gives Order_ID, lowercasing gives order_id, and replacing the underscore gives order id. The name customer-id needs the second replacement because changing underscores does not affect hyphens. The example result therefore matches precisely the operations shown, with no implied extra transformation hidden in the explanation.
Clean and Trim solve different problems
Text.Clean removes control characters. Text.Trim removes leading and trailing whitespace or specified trimming characters. In this version, internal ordinary spaces are not collapsed into one. That limitation should be clear before the expression is reused across many files.
If a heading contains a tab between two fragments, removing it can join those fragments. Cleaning does not necessarily insert a neat separating space. When the separator has meaning, I first agree how to replace it and then remove other control characters. The operation order can change the result.
I avoid deleting every character that merely looks unusual on screen. First I identify the character and why it appears in the export. Otherwise I may build a rule that suits one file and damages another source’s headings. Understanding the source remains part of the task even when the final expression stays short.
Choose a culture for letter case
Text.Lower accepts an optional culture parameter. I specify en-US because this exercise uses English technical names. That is not a universal setting for every language. A different naming language requires a deliberate choice of case-conversion rules.
The same consideration applies to Text.Proper, which changes the case at the beginning of words. It can be useful for labels but does not know your organisation’s preferred abbreviations. A name containing ID or SKU may end up with capitalisation you do not want readers to see.
I prefer a separate presentation layer to repeatedly changing technical names for different views. A stable schema makes refresh steps easier to follow. A readable report can still use suitable labels for measures and fields exposed to its audience. The calculation pipeline and the display do not have to use identical spelling conventions.
A replacement is not a complete naming standard
Text.Replace replaces the specified text and is case-sensitive. Our separators are simple and lowercasing happens first. Nothing in this expression identifies synonyms or translates a column’s meaning automatically. It implements only the operations explicitly included in the code.
For sales__total, replacing both underscores produces two spaces. If a heading already has spaces beside a hyphen, replacing the hyphen with another space can increase their number. These are predictable consequences of the rule. I would not describe it as removing every unnecessary space when it does not do that.
I can extend the convention later, but first I establish whether collapsing spaces is necessary and how it affects uniqueness. Each extra operation should have a specific purpose and an explicit example. Making a formula more elaborate simply for elegance often makes maintenance harder for the next person who receives the query.

Look for collisions before relying on the names
Order_ID and Order-ID both map to order id under our transformation. They may nevertheless represent different fields. Power Query needs distinguishable column names; the Table.TransformColumnNames documentation illustrates generated variations when names conflict. I do not treat an automatic suffix as a business definition.
Before publication, I compare the input names with their proposed outputs. When two proposals coincide, I establish whether they are duplicate representations of one field or different information needing distinct labels. I do not remove a column simply because its heading can be cleaned into the same text as another heading.
This matters especially for price and cost, net and gross amounts, or identifiers from different systems. An aggressive rule can erase precisely the fragment that distinguished their meanings. A tidier column list does not compensate for an incorrect analysis. I preserve the mapping so that each field’s origin remains understandable.
Use explicit mappings for synonyms
If an agreed old heading needs a specific new name, Table.RenameColumns is suitable. I supply name pairs instead of trying to infer meaning from spelling:
= Table.RenameColumns(
#"Previous Step",
{{"Customer Number", "customer id"}, {"Net Revenue", "net sales"}}
)
This example assumes the source columns exist and the target names do not conflict with others. A missing source field produces an error by default. For a required identifier, that can be a useful indication of a source change. I do not automatically add MissingField.Ignore merely to make a refresh stop complaining.
A mapping is appropriate only after equivalence has been confirmed. Customer Number might identify a customer in one source and a branch in another. The function name cannot resolve that distinction. The rule records a decision already made from the source definitions, rather than providing evidence for the decision itself.
Place the step before references to target names
I normalise names after obtaining the actual headings but before steps that should use the standard names. If types were assigned using earlier headings, I inspect those references. An automatically created Changed Type step may still expect the original spelling of a column.
I do not move the transformation blindly to the beginning of every query. Earlier in the import, the columns may still be called Column1 and Column2, with the intended headings sitting in the first data row. Transforming those temporary names will not clean the future headings. First I understand the import’s sequence.
After changing names, I inspect subsequent steps for join keys, selected columns, expanded tables and type assignments. Each can depend on headings. A successful preview at one point does not establish that every schema dependency has been updated or that the next source file will satisfy the same requirements.
Compare the schema of each file
A recurring monthly import needs compatible names before its data is combined. If two files use different headings for the same field, the result can contain two partly empty columns. I therefore inspect normalisation for every source variant instead of judging only the final combined table.
I check required columns, unexpected additions and missing earlier fields. A new name may be cosmetic, but it can also signal a changed definition. Sales without a currency qualifier may differ from Sales EUR. Removing the unit to make files easier to combine would hide a question that the analysis actually needs answered.
When merging queries, a key’s heading is only one part of the problem. Its values, type and uniqueness require separate checks. Likewise, standardising monthly file headings does not establish data compatibility. It is a preparation stage followed by further questions about meaning and completeness.
Keep headings separate from column contents
Renaming Order_ID to order id leaves the identifier values unchanged. If some contain spaces, inconsistent types or different formats, they still need their own transformation. Table.TransformColumnNames does not inspect and repair every cell as an incidental effect of changing the heading.
Amounts and dates follow the same principle. A clean heading such as order date does not prove that the column contains dates or that conversion used the appropriate culture. I distinguish schema, type and value checks so that every step has a clear responsibility. When something fails, I can then identify which part needs attention.
I also avoid promising a specific number of hours saved. The benefit depends on import frequency, source variation and exception handling. A sensible aim is a predictable rule that does not need rebuilding for an identical file. Genuine exceptions still need decisions, even when the routine part becomes automatic.
Decide what happens when the export changes
Suppose the next file provides separate sales net and sales gross columns instead of Sales_Total. That calls for checking the source definition. I should not rename both to sales total and continue as before. I need to know which value feeds the report and whether earlier periods remain comparable.
I also establish when the import should stop. A missing required identifier differs from a new optional comment column. Making that distinction reduces both silent damage and unnecessary alarms. The rule should make sense to the person who receives the refresh failure, without requiring them to reverse-engineer the entire query.
The process documentation should contain the current convention and mapping. The task tracker holds execution history. The next maintainer needs to understand which names are required now and why. They should not need to read every abandoned expression to handle another monthly export safely.
Do not accidentally erase language-specific letters
Some older examples suggest keeping only letters from a to z and digits. Such an allowed-character list deletes other letters instead of converting them into expected counterparts. Meaningful parts of a Polish heading can disappear. If the destination requires ASCII, I need an explicit transliteration rule or a heading mapping.
Deleting a character and replacing it with another character are different decisions. I do not treat them as interchangeable. Names can become empty or collapse into identical outputs after deletion. For multilingual sources I therefore start with actual headings and destination requirements rather than forcing the data to fit a conveniently discovered function.
I am equally careful with programming-style names. ProductName is generally called PascalCase, while productName is camelCase. Capitalising word beginnings and removing spaces does not establish correct handling of abbreviations, digits or separators. This article deliberately stays with its explicitly described lowercase-and-spaces convention.
Check whether the rule can be repeated sensibly
A well-defined normalisation should have a predictable effect when it receives names that are already clean. For order id, our expression leaves the spelling unchanged. This is useful when a step is reused or files move between stages. Collisions and extra separators still need separate consideration because their meaning is not established by that one example.
When reviewing the solution, I compare headings and representative records before and after the operation. The intended change concerns naming, with no change to row count, column count or the interpretation of amounts. I then inspect another period’s file with a known schema. That review helps establish whether the convention fits the actual process for which it was designed.
Exercise: challenge the rule with awkward headings
Take the four initial headings and write down each transformation stage. Then add sales__total and put Order-ID beside Order_ID. Identify where double spaces appear and where two proposed names become identical. Leave column values unchanged because this exercise concerns only the schema.
Next add Revenue and decide whether it can honestly become sales total. If its definition is missing, write a question for the source owner rather than guessing. Finally identify the step where you would assign the amount’s type and explain its position relative to renaming. This connects a naming rule to the full preparation process.
If you prefer to begin with the interface, read about columns from examples. I use the same principle in both cases: describe the expected outcome and its limits, then choose a transformation. Short code becomes useful when its exact responsibility is understood.
Key takeaways
Does this function clean cell values?
No, it transforms column names. Cell contents and data types need separate operations.
Can the rule establish that Amount means Revenue?
No, equivalent meaning must be confirmed separately. I use explicit mappings for agreed synonyms.
What if two names become identical?
I compare the fields and agree on their meanings. An automatically generated suffix is not a definition of a column.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
