【Excel】Power Queryで和暦の日付を読み込めない時の変換方法

ExcelのPower QueryでCSVやデータベースから和暦の日付(例:令和5年4月1日)を読み込もうとすると、日付として認識されず文字列のままになる、またはエラーが発生することがあります。これはPower Queryの標準設定が西暦の日付書式を前提としているためです。本記事では、和暦の日付データを正しく日付型に変換する方法を、具体的な手順とともに解説します。原因の切り分け方やよくある失敗パターンも取り上げますので、実際の業務でお役立てください。

1. 和暦の日付が読み込めない原因

Power Queryが和暦の日付を正しく認識できない主な原因は、データが「文字列」として扱われていることと、和暦の書式が標準の日付書式と異なることです。Power Queryはデフォルトで西暦の日付書式(例:2024/4/1)を期待するため、和暦の「R5.4.1」や「令和5年4月1日」などはそのままでは日付に変換できません。また、データが全角文字や半角カタカナで混在している場合も変換に失敗する原因となります。

特に、CSVファイルを読み込む際にPower Queryが自動的に列のデータ型を判断しますが、和暦の日付は多くの場合「テキスト」と判定されます。この自動判定をそのままにすると、後続の処理で日付として扱えないため、変換が必要になります。

2. 事前確認とデータの状態把握

変換を行う前に、データがどのような形式で保存されているかを確認してください。以下の観点でチェックします。

2-1. 和暦の表記パターンを確認する

表記は大きく分けて次の3パターンがあります。

• 元号を含む漢字表記:令和5年4月1日、平成31年1月7日
• 元号の英字略号+数字:R5.4.1、H31.1.7
• 元号を含まない数字のみ(和暦年):5/4/1(元号が別列にある場合など)

また、全角文字か半角文字か、区切り文字が「/」「-」「.」のどれかも影響します。

2-2. Power Queryエディターでエラーの詳細を確認する

読み込み後に列のデータ型が「テキスト」になっている場合、変換を試みるとエラーになることがあります。Power Queryエディターの「データ型の変更」で「日付」を選択した際に、エラー行だけが「Error」と表示されるケースです。この場合は、変換の前にテキストを整形する必要があります。また、列の先頭の行をプレビューして、データに一貫性があるかも確認しましょう。

3. 和暦の日付を変換する具体的な手順

ここでは、代表的な2つの方法を説明します。1つは「カスタム列を使用して文字列を日付に変換する方法」、もう1つは「ロケールを変更して変換する方法」です。状況に応じて使い分けてください。

3-1. カスタム列を使った変換(元号ありの漢字表記の場合)

「令和5年4月1日」のようなデータを変換するには、Power QueryのM言語を使って文字列を解析します。以下の手順で行います。

1. Power Queryエディターを開き、対象の列を選択します。
2. 「列の追加」タブから「カスタム列」をクリックします。
3. 新しい列名を「日付変換」などと入力し、以下のM言語式を入力します。
   let
元号 = Text.Start([和暦列], 2),
年 = Number.FromText(Text.BetweenDelimiters([和暦列], "年", "年")),
月 = Number.FromText(Text.BetweenDelimiters([和暦列], "年", "月")),
日 = Number.FromText(Text.BetweenDelimiters([和暦列], "月", "日")),
西暦年 = if 元号 = "令和" then 年 + 2018 else if 元号 = "平成" then 年 + 1988 else if 元号 = "昭和" then 年 + 1925 else null
in
if 西暦年 <> null then #date(西暦年, 月, 日) else null
   ※データに応じて元号の対応を追加してください。
4. 「OK」をクリックすると、新しい列に日付が生成されます。
5. 元の列は不要であれば削除します。最後に「閉じて読み込む」でExcelに反映します。

3-2. ロケールを変更して変換する方法(英字略号の場合)

「R5.4.1」のような英字略号の場合、Power Queryのロケール設定を「日本語」に変更することで変換できる場合があります。手順は以下のとおりです。

1. Power Queryエディターで対象の列を選択し、右クリックから「変更の種類」→「ロケールを使用して」を選択します。
2. 表示されるダイアログで、「データ型」を「日付」に、「ロケール」を「日本語(日本)」に設定します。
3. 「OK」をクリックします。Power Queryが和暦を西暦に自動変換します。
4. ただし、この方法は元号の略号(R、H、S)と「年/月/日」の表記にしか対応しないため、漢字表記では正しく変換されません。
5. 変換後、列のデータ型が日付になっていることを確認します。

4. 状況別比較表

和暦の表記と変換方法の組み合わせを下表にまとめました。自分のデータに合った方法を選んでください。

和暦の表記例	推奨変換方法	注意点
令和5年4月1日	カスタム列(M言語式)	元号の対応を漏れなく記述する必要があります。
R5.4.1	ロケール変更(日本語)	区切り文字が「/」や「-」の場合は事前に置換が必要です。
H31.1.7	ロケール変更(日本語)	元号が「H」「S」「R」のみ対応。他はカスタム列。
5/4/1(元号が別列)	カスタム列+別列の元号を結合	元号列と日付列を組み合わせて処理します。

5. 失敗パターンと注意点

5-1. 全角文字と半角文字の混在

データに全角の数字やアルファベットが含まれている場合、ロケール変更では変換に失敗します。事前に「置換」や「Text.Trim」を使って半角に統一してください。

5-2. 元号の判別が曖昧な場合

例えば「5/4/1」のように元号が省略されている場合、どの元号に該当するか推測できません。このようなデータでは、別の列に元号情報があるか、あるいはデータの範囲を確認して適切な元号を仮定する必要があります。誤った元号で変換すると日付が大きくずれるため、注意が必要です。

5-3. 変換後にエラーが残る場合

変換後も一部の行がErrorになる場合、その行は元のデータに異常がある可能性があります。該当行をフィルターで抽出して、手動で修正するか、該当行を除外する処理を検討してください。

6. 管理者に確認すべきこと

会社のPCで作業する場合、Power Queryの設定変更が制限されていることがあります。特に、カスタム列のM言語式を使用する場合は問題ありませんが、ロケール変更や地域設定の変更はセキュリティポリシーによってブロックされる場合があります。以下の点を管理者に確認してください。

• Power Queryのロケール設定変更が許可されているか。
• Excelの「地域の設定」を変更する必要がある場合、管理者権限が必要か。
• 共有データソースにアクセスする際、変換ロジックを共有するか、個人のクエリとして持つかの方針。

これらの確認を事前に行うことで、トラブルを未然に防げます。

7. よくある質問(FAQ)

Q1. ロケール変更後も「Error」が表示されます。どうすればいいですか?
A. データがロケール変更で対応できる書式か確認してください。例えば「R5年4月1日」のように年号の後に「年」が入ると、ロケール変更では変換できません。その場合はカスタム列を使用してください。

Q2. M言語の式が複雑でエラーになります。簡略化する方法はありますか?
A. シンプルなケースでは「Text.Split」で区切り文字ごとに分割してから組み立てる方法もあります。また、Microsoftの公式サンプルやコミュニティのコードを参照すると効率的です。

Q3. 変換後の日付が1日ずれているように見えます。原因は?
A. 元号から西暦への換算が間違っている可能性があります。例えば、令和元年は2019年ですが、和暦の年数と西暦の対応を確認してください。また、月日が逆になっている場合も考えられます。

8. まとめ

Power Queryで和暦の日付を読み込めない問題は、データの表記形式を正確に把握し、適切な変換方法を選ぶことで解決できます。特に元号の表記が漢字か英字かで推奨方法が異なりますので、本記事の比較表や手順を参考にして作業を進めてください。カスタム列を使う方法は汎用性が高い反面、M言語の知識が必要です。一方、ロケール変更は簡単ですが対応範囲が限られます。データの発生元や後続の処理も考慮して、最適な方法を選びましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】