【Excel】Power Queryで日付の月と日が逆になる時のロケール変更手順

ExcelのPower Queryを使ってデータを読み込んだとき、「2024/03/04」というデータが「2024/04/03」と解釈されてしまう、という経験はありませんか。これは日付の月と日が逆転する現象で、特に海外のデータやシステムから出力されたCSVファイルで頻繁に発生します。この問題の根本原因は、Power Queryがデータのロケールを正しく認識できていないことにあります。本記事では、ロケール設定を変更して正しい日付を取得する手順を詳しく解説します。また、よくある失敗パターンや管理者に確認すべきポイントも併せて説明しますので、トラブル解決の参考にしてください。

1. なぜ日付が逆になるのか?原因を理解する

Power Queryが日付データを解釈する際、システムのロケール(国と地域の設定)に基づいて月と日を判断します。例えば、日本では「2024/03/04」は2024年3月4日ですが、アメリカなどの月/日/年の形式では「2024/03/04」は2024年4月3日と解釈されます。Power Queryはデータを読み込むとき、自動的にデータ型を検出し、その際にWindowsの地域設定が参照されます。このため、日本以外のロケールが適用されると月日が逆転してしまうのです。

ロケールと日付形式の関係

日付の表記には主に次の3種類があります。

• 月/日/年(MM/dd/yyyy):アメリカなどで一般的。
• 日/月/年(dd/MM/yyyy):イギリスやヨーロッパの多くの国で使用。
• 年/月/日(yyyy/MM/dd):日本や中国、ISO標準形式。

Power Queryはデータの区切り文字(スラッシュやハイフン)と数値の範囲から形式を推定しますが、日付の値が12以下の場合、月と日を区別できずに誤った解釈をすることがあります。特に「03/04」のような値は3月4日なのか4月3日なのか自動判定が難しいため、問題が発生します。

Power Queryのデータ型推論

Power Queryはデータを読み込む際、各列のデータ型を自動検出します。このとき、日付列に対して「ロケール」を使った変換が行われます。自動検出ではWindowsの地域設定(例:日本語日本)が使用されますが、この設定が実際のデータ形式と合わないと誤変換が起きます。また、CSVファイルを読み込む際に「ロケール」が指定されていない場合も同様の問題が発生します。この問題を解決するには、Power Query内で正しいロケールを明示的に指定する必要があります。

2. ロケール変更の具体的な手順(Power Queryエディター)

ここでは、Power Queryで日付列のロケールを変更する手順を解説します。例として、アメリカ形式(MM/dd/yyyy)のデータを正しく読み込む方法を示しますが、他のロケールにも応用できます。

1. Excelでデータを読み込み、Power Queryエディターを開きます。[データ]タブ → [テーブルまたは範囲から]、または[データの取得]からデータソースを選択してください。
2. 読み込まれたデータが表示されたら、日付が逆になってしまっている列(例:日付列)を選択します。列の左側にアイコンが表示され、データ型が「日付」や「日付/時刻」になっていることを確認します。
3. 列ヘッダーの左側にあるデータ型アイコン(日付の場合はカレンダーアイコン)をクリックし、[ロケールを使用]を選択します。直接、列ヘッダーを右クリックして[データ型の変更]→[ロケールを使用]でも構いません。
4. [ロケール]ダイアログボックスが開きます。[データ型]は「日付」または「日付/時刻」を選択し、[ロケール]ドロップダウンから適切なロケールを選びます。例えば、データがアメリカ形式(MM/dd/yyyy)の場合は「英語(米国)」、ヨーロッパ形式(dd/MM/yyyy)の場合は「英語(イギリス)」や「ドイツ語(ドイツ)」など、実際のデータ形式に対応するロケールを指定します。
5. OKをクリックすると、データが正しい日付に変換されます。このとき、クエリ設定の[適用したステップ]に「Changed Type with Locale」というステップが追加されます。数式バーで確認すると、Table.TransformColumnTypes(ソース, {{"日付", type date}}, "en-US") のようにロケールが指定されていることが分かります。
6. 変更を適用してExcelに読み込み直すには、[ホーム]タブの[閉じて読み込む]をクリックします。Power Queryエディターを閉じるときに[保持]を選択してください。

この手順により、Power Queryが列全体の日付を指定されたロケールで解釈するようになります。同じデータソースを再読み込みしても設定は保持されるため、一度設定すれば以降は自動的に正しい変換が行われます。

3. 代替方法:書式設定とロケール指定の違い

日付の逆転を修正する方法として、Excel側で書式設定を変更する方法もありますが、根本的な解決にはなりません。次の表で各方法を比較します。

方法	概要	効果	注意点
Power Queryのロケール変更	データ型変換時にロケールを指定	元データの意味を正しく解釈	データ形式に合ったロケールを選ぶ必要がある
Excelのセルの書式設定	表示形式をユーザー設定で変更	見た目だけを変更(実データは変わらない)	値自体が逆のままなので集計やフィルターで誤った結果になる可能性がある
ソースデータの前処理	元のCSVやデータベースで日付形式を統一	根本的な解決	データ提供元の変更が必要で、社内で制御できない場合がある

Power Queryのロケール変更は、データを正しい日付値として解釈するため、後続の処理でも正しく扱えます。一方、Excelの書式設定だけでは内部のシリアル値が誤ったままのため、月と日が入れ替わった値で計算されるリスクがあります。そのため、Power Queryでロケールを指定する方法が最も推奨されます。

4. よくある失敗パターンと注意点

ロケールを変更しても反映されない

ロケールを指定したにもかかわらず、日付が逆転したままの場合があります。原因として、以下の点を確認してください。

• データ型が「テキスト」のままになっていないか。ロケール変更は日付型に対してのみ機能するため、まずデータ型を「日付」に変更してからロケールを指定する必要があります。
• 複数の列にロケールを適用する場合、各列に対して個別に設定する必要があります。一度にすべての列に適用する方法はないため、列ごとに設定してください。
• Power QueryのバージョンやExcelのバージョンによって、ロケールの選択肢が異なる場合があります。最新の更新プログラムを適用しているか確認しましょう。

読み込み後にExcelで日付が変わってしまう

Power Queryで正しく変換したにもかかわらず、Excelシートに読み込んだ後に日付が元に戻ってしまうことがあります。これは、Excelの地域設定がPower Queryの設定よりも優先されるためです。対策として、Excelでも該当セルの書式設定を確認し、表示形式を「日付」にして適切な地域を選択するか、またはPower Queryで日付をテキストとして読み込み、Excel側でDATEVALUE関数を使って変換する方法もあります。ただし、テキストとして読み込むと日付としての機能が失われるため、計算に使う場合は注意が必要です。

5. 管理者や情報システム部門に確認すべきこと

会社のPCでは、セキュリティポリシーによりWindowsの地域設定を変更できない場合があります。その場合でも、Power Query内でロケールを指定する方法はユーザー権限で実行できるため、問題を回避できます。ただし、以下の点については管理者に確認しておくとスムーズです。

• Power Queryで使用するロケールの一覧が制限されていないか。組織によっては特定のロケールしか利用できない設定になっている可能性があります。
• データソースが社内システムの場合、そのシステムの日付形式を統一できないか。データ提供元に依頼して、日付を「yyyy-MM-dd」のような曖昧さのない形式に統一してもらうのが理想的です。
• Power Queryのクエリを共有する場合、ロケール設定も一緒に保存されるため、他のユーザーが同じクエリを使うときにも正しく動作します。ただし、相手のExcelの地域設定が異なると、再度変換が必要になることもあるため、注意してください。

6. よくある質問(FAQ)

Q1. ロケールを変更しても日付が変わらないのはなぜですか?

データ型が日付になっていない可能性があります。ロケール変更は日付型の列に対してのみ有効です。まず列のデータ型を「日付」に変更してから、再度ロケールを指定してください。また、数式バーでTable.TransformColumnTypesにロケールが正しく指定されているか確認しましょう。

Q2. ロケールの選択肢に「日本語(日本)」しかありません。どうすればよいですか?

Power QueryはWindowsの言語パックと地域設定に応じてロケールの一覧を表示します。必要なロケールが表示されない場合、Windowsの「地域」設定で「管理」タブから「システム ロケールの変更」を行い、対象の言語パックをインストールすることで追加されることがあります。ただし、会社PCでは管理者権限が必要な場合が多いため、情報システム部門に相談してください。

Q3. 毎回手動でロケールを設定するのは面倒です。自動化する方法はありますか?

Power Queryのクエリは保存されるため、一度設定すれば次回以降は自動的に適用されます。また、M言語を使ってカスタム関数を作成し、複数のクエリで使い回すことも可能です。ただし、データソースの形式が頻繁に変わる場合は、毎回ロケールを確認する必要があります。

7. まとめ

Power Queryで日付の月と日が逆転する問題は、ロケール設定を正しく指定することで解決できます。Power Queryエディターの「ロケールを使用」機能を活用して、データの実際の形式に合ったロケールを選択することが重要です。Excelの書式設定だけでは内部の値が正しくならないため、必ずPower Query側で対応しましょう。会社PCの制限でロケールが変更できない場合は、管理者に相談の上でデータソースの形式統一を検討するのも有効な手段です。

📊

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

🏆

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