About this article
This article was created using an automated generation workflow powered by generative AI. It is based on the official Microsoft Learn Power Query M specifications and existing Daily-Code-Samples, structured with dummy tables to illustrate column name variations.Verification Status: 📘 Official specifications and existing samples verified / Excel actual device not tested
If updates fail simply because "Employee Code" changed to "Employee ID" in the monthly CSV, adding a layer in Power Query to map input names to standard names makes maintenance much easier. However, ignoring missing columns entirely can hide anomalies, sothe key is to explicitly specify only the allowed variations.
First test
Paste the following into a blank Power Query query:
let
Source = #table(
{"従業員ID", "氏名"},
{{"A001", "パンダ"}, {"A002", "コアラ"}}
),
Renamed = Table.RenameColumns(
Source,
{{"従業員ID", "社員コード"}},
MissingField.Ignore
),
Output = Table.SelectColumns(
Renamed,
{"社員コード", "氏名"},
MissingField.UseNull
)
in
Output
Look here
The input becomes従業員IDbut the output becomes社員コード.Table.SelectColumnsIn addition, you can fix the final columns usingnulland handle missing columns with
.Table.RenameColumnsAccording to Microsoft Learn, functions normally error out on missing columns, but you can specifyMissingField.Ignorein the third argument.Table.SelectColumnsAlso, inMissingField.UseNullyou can specify
Try changing one place
従業員IDChange社員番号to社員コードand re-run. Since it is not in the allowed list this time,nullbecomes
.This is crucial.Ensuring processes do not failand not missing anomaliesare two different things. For business data, it is safer not to automatically guess and standardize unknown column names.
Why design it this way?
flowchart LR
A[毎月CSV] --> B[許容する列名ゆれを標準化]
B --> C[必要列を固定]
C --> D{不足列?}
D -- なし --> E[通常処理]
D -- あり --> F[null/警告として検知]
By placing an "input contract normalization layer" midway through Power Query, subsequent aggregation formulas do not need to worry about monthly naming variations. At the same time, unknown changes can be flagged as warnings.
For practical use
This approach can be used for HR CSVs, survey results, Excel files collected from various departments, and monthly files from external contractors. In practice, managing permitted aliases as a table and loggingoriginal column names, standard column names, and missing columnsmakes it easier to determine whether a change is an intentional specification update or just a naming variation.
Before deploying to production, verify four cases with dummy data: ① added columns, ② removed columns, ③ two synonymous columns arriving simultaneously, and ④ changed data types. Especially when both "Employee Code" and "Employee ID" exist at the same time, it is safer to design the system so that it does not silently adopt either one.
