About this article
This article was created using an automated generation workflow powered by generative AI. We have verified the specifications for Table.RenameColumns, Table.SelectColumns, and MissingField in Power Query M, and revised the existing samples to follow a safer design that does not silently discard multiple candidates.Verification Status: 📘 Power Query M official specifications verified, sample safety reviewed, execution on actual data unverified
Making monthly CSV imports resilient to column name changes — Absorbing column name variations in Power Query M
If only the column names change in monthly CSVs, such as 社員コード、従業員ID、社員ID, you can map multiple aliases to a single standard name on the Power Query side to stabilize downstream processing.
However, it is a different story if both 社員コード and 社員ID exist in the same CSV at the same time. Instead of automatically deciding which one is correct, the safer approach is to stop the process with an error.
Define the target schema first
The final columns for this case are the following four:
社員ID / 名前 / 部門 / 金額
The input side allows the following aliases:
| Standard Name | Allowed Aliases |
|---|---|
| Employee ID | Employee Code / Employee ID / Staff ID |
| Name | Full Name / Name / Employee Name |
| Department | Affiliation / Department / Division |
Mapping names only when exactly one candidate is found
NormalizeOne = (tbl as table, candidates as list, target as text) as table =>
let
names = Table.ColumnNames(tbl),
matches = List.Select(candidates, each List.Contains(names, _)),
result =
if List.Count(matches) = 0 then
tbl
else if List.Count(matches) > 1 then
error Error.Record(
"SchemaConflict",
"候補列が複数あります。確認してください。",
[Target = target]
)
else if matches{0} = target then
tbl
else
Table.RenameColumns(tbl, {{matches{0}, target}}, MissingField.Ignore)
in
result
Table.RenameColumns is a function used to rename columns. Here, we rename to the standard name only when exactly one of the existing candidates is found.
Why we do not automatically adopt multiple candidates
For example, suppose the CSV contains the following two columns:
社員コード = A001 社員ID = B999
If the logic is 'adopt the first one found,' the other one would be silently dropped. However, this is not a simple column name variation; it is a state wherethe meaning of the input data is ambiguous.
Therefore, the current sample is designed to stop with SchemaConflict. In automation, it is important not only to handle the happy path but also to define the boundaries where automatic decisions should not be made.
Fill missing columns with null
After standardizing the column names, use Table.SelectColumns and MissingField.UseNull to align the final columns.
Table.SelectColumns(
Step3,
{"社員ID", "名前", "部門", "金額"},
MissingField.UseNull
)
For example, if only this month the 金額 column is missing, instead of dropping the column itself, we create a null column with 金額 values. This makes it easier to keep the column structure consistent for subsequent steps.
flowchart LR
A[毎月のCSV] --> B[候補列を確認]
B --> C{同義候補が複数?}
C -- はい --> D[SchemaConflictで停止]
C -- いいえ --> E[標準列名へrename]
E --> F[必要列をSelect]
F --> G[不足列はnull補完]
G --> H[一定のスキーマ]
Stopping correctly is better than not stopping at all
If a single column is missing, the process can continue by filling it with nulls. On the other hand, if two columns arrive simultaneously and it is unclear which one is correct, the process should be stopped.
By introducing this distinction, Power Query becomes more than just a mechanism to hide errors; it becomes a system thatabsorbs expected variations while detecting changes with ambiguous meanings.
