【Excel】Power Queryで日付と時刻がずれる時のタイムゾーン確認

Power Queryでデータを取り込む際、日付や時刻が実際の値と異なって表示されることがあります。特に海外のデータやクラウドサービスから取得した場合、タイムゾーンの扱いが原因でずれが生じることがあります。この記事では、Power Queryにおける日付と時刻のずれを解消するために、タイムゾーンの確認手順を中心に解説します。まずは原因を切り分けるための基本的なポイントを押さえましょう。

なぜ日付と時刻がずれるのか:タイムゾーン原因を整理

Power Queryで日付や時刻がずれる原因は、大きく分けて3つあります。1つ目は、データソース側にタイムゾーン情報が含まれているにもかかわらず、Power Queryがそれを無視して解釈するケースです。例えば、UTCで保存された日時を日本時間(UTC+9)として読み込むと、9時間のずれが生じます。2つ目は、元データにタイムゾーン情報がなく、Power Queryが環境設定に基づいて勝手に変換する場合です。3つ目は、Power Queryのデータ型が「日付/時刻」と「日付/時刻/タイムゾーン」のどちらであるかにより、表示値が変わることです。

具体的な例を挙げます。SharePointリストからエクスポートしたデータの「作成日時」は通常UTCで保存されます。Power Queryでそのまま読み込むと、デフォルトでは「日付/時刻/タイムゾーン」型として扱われますが、ロケール設定によっては自動的にローカル時刻に変換されることがあります。一方、CSVファイルの日付データにはタイムゾーン情報がないため、Power Queryは単なる文字列として扱い、後で型変換する際に環境のタイムゾーンを適用します。このように、原因はデータソースとPower Queryの設定の組み合わせで決まります。

タイムゾーンを確認するためのPower Query手順

ずれが疑われる場合、以下の手順でタイムゾーンの影響を切り分けることができます。操作はPower Queryエディタ内で行います。

1. 対象のクエリを右クリックし、「編集」を選択してPower Queryエディタを開きます。
2. 該当する日付または時刻の列を選択します。列ヘッダーの左側に表示されているデータ型アイコンを確認してください。
3. 「変換」タブの「データ型」グループから、現在のデータ型を把握します。例えば「日付/時刻」や「日付/時刻/タイムゾーン」と表示されます。
4. データ型が「日付/時刻」の場合、タイムゾーン情報が失われている可能性があります。この列を右クリックし、「データ型の変更」→「日付/時刻/タイムゾーン」に変更してみてください。すると、元の値にUTCが仮定されるか、またはエラーが発生することがあります。
5. データ型がすでに「日付/時刻/タイムゾーン」の場合、列の値にタイムゾーンオフセット(例:+09:00)が含まれているか確認します。含まれていない場合、Power Queryが自動的にUTCと解釈している可能性があります。
6. 「ホーム」タブの「データソース設定」→「グローバルデータソース設定」で、Power Queryの地域設定を確認します。Excelの場合、Power Queryメニューの「オプションと設定」から「クエリオプション」→「地域設定」でタイムゾーンを指定できます。
7. 必要に応じて、「変換」タブの「タイムゾーン」グループにある「ローカルタイムゾーンに変換」または「タイムゾーンの追加」を適用します。例えば、明らかにUTCで保存されているデータの場合、「タイムゾーンの追加」で「UTC」を指定し、その後「ローカルタイムゾーンに変換」で環境のタイムゾーン(例:東京)に変換します。

これらの手順を実施することで、ずれの原因がデータ型の解釈なのか、変換ロジックなのかを特定できます。特に手順4と6は重要です。

ずれが発生する代表的なデータソースとその対応

データソースによって、タイムゾーンの扱いは異なります。以下の表に代表的なケースと推奨対応をまとめました。

データソース	元の日時形式	Power Queryのデフォルト動作	ずれの方向	推奨される対応
SharePointリスト	UTC(例:2023-10-01T03:00:00Z)	「日付/時刻/タイムゾーン」として読み込み、ローカル表示に変換	日本時間で9時間進む	変換せずそのまま利用するか、明示的にUTCのまま保持
SQL Server(datetime型)	タイムゾーン情報なし(例:2023-10-01 12:00:00)	「日付/時刻」として読み込み、環境のタイムゾーンを適用しない	ずれは発生しないが、タイムゾーン解釈が不明	必要に応じて「日付/時刻/タイムゾーン」に変更し、明示的にタイムゾーンを追加
CSVファイル	文字列(例:”2023/10/1 12:00″)	文字列として読み込み、型推測で「日付/時刻」に変換される	環境のタイムゾーンが適用される(Windows設定依存)	変換前にタイムゾーン情報を付加するか、地域設定を統一
Salesforce API	UTC(ISO 8601)	「日付/時刻/タイムゾーン」として読み込み、ローカル表示に変換	日本時間で9時間進む	UTCのまま保持するか、変換しない設定にする

各データソースの特性を理解した上で、適切な変換ステップを追加してください。特に注意すべきは、CSVやSQL Serverのようにタイムゾーン情報がないデータには、明示的にタイムゾーンを指定する必要がある点です。

タイムゾーン変換の失敗パターンと対策

パターン1:夏時間の考慮漏れ

海外のデータを扱う場合、夏時間(サマータイム)が適用されている地域では、時期によってオフセットが変わります。Power Queryの「ローカルタイムゾーンに変換」機能は、Windowsのタイムゾーン設定に依存するため、夏時間の自動調整が正しく行われるとは限りません。特に、古いデータや将来の日付を含む場合は、変換結果に誤差が生じることがあります。対策としては、変換前にタイムゾーンを固定(例:常にUTC+9)するか、日付ごとにオフセットを手動で調整するロジックを組む必要があります。

パターン2:日付のみのデータにタイムゾーンを追加してしまう

「日付」型として保存されているデータに「日付/時刻/タイムゾーン」型を適用すると、時刻部分が0時(00:00:00)として扱われ、オフセットが加算されることで日付が変わってしまうことがあります。例えば、日付「2023-10-01」にUTC+9を適用すると「2023-10-01 09:00:00」として解釈される可能性があります。これは避けるべき変換です。日付データは「日付」型のまま保持し、時刻が必要な場合のみ変換するようにしてください。

パターン3:複数タイムゾーンが混在するデータ

1つの列に異なるタイムゾーンの日時が混在している場合(例:海外支店のタイムスタンプ)、一律の変換をかけると誤った値になります。このようなデータは、まずタイムゾーン情報を別列に抽出するか、元のタイムゾーンを特定できるルールを適用してから変換する必要があります。Power Queryでは、条件列を使用してオフセットを切り替える方法が有効です。

管理者に確認すべき設定項目

会社のPCでPower Queryを使用する場合、以下の設定が管理者によって制限されていることがあります。ずれの原因がユーザー環境にある場合、管理者への確認が必要です。

• Windowsのタイムゾーン設定: Power QueryはWindowsのタイムゾーン設定を参照します。会社PCでタイムゾーンが固定されている場合、正しいローカル時刻が適用されない可能性があります。
• Power Queryの地域設定: ExcelのオプションやPower BI Desktopのオプションで、地域設定が変更できないようにポリシーでロックされていることがあります。
• データソースのアクセス権限: SharePointやデータベースの日時データが、組織の標準でどのタイムゾーンで保存されるかは、管理者のポリシーに依存します。可能であれば、データの格納形式を確認してください。
• Power Queryのキャッシュ設定: クエリ結果がキャッシュされている場合、古いタイムゾーン設定のまま動作することがあります。管理者にクエリキャッシュのクリア方法を確認しましょう。

これらの情報を管理者と共有することで、組織全体で一貫したタイムゾーン管理が可能になります。

よくある質問(FAQ)

Q: Power Queryでタイムゾーンを完全に無視して、元の日時をそのまま表示するにはどうすればよいですか?
A: データ型を「日付/時刻」に変更するとタイムゾーン情報が削除されます。ただし、その列の値がタイムゾーンオフセットを含んでいる場合は、変換時にエラーが発生するか、強制的にUTCと解釈されることがあります。その場合は、まず「テキスト」型として読み込み、日時部分を抽出してから「日付/時刻」に変換してください。

Q: 日本時間(UTC+9)とUTCの間で9時間のずれが生じます。簡単に修正する方法はありますか?
A: 「変換」タブの「タイムゾーン」→「時間の加算」を使用して、手動で9時間を加減算する方法があります。ただし、夏時間を考慮する必要がある場合は、専用の関数(DateTime.AddZoneなど)を使ったカスタム列を作成することをお勧めします。

Q: Power Query Desktop(Power BI)とExcelのPower Queryでタイムゾーンの動作は異なりますか?
A: 基本的な動作は同じですが、地域設定の既定値が異なる場合があります。Power BI Desktopではオプションで「現在のタイムゾーン」を設定できますが、ExcelではWindowsの地域設定に依存する部分が大きいです。また、Power BI Serviceに発行する際は、サービスのタイムゾーン設定も影響するため、注意が必要です。

Q: 夏時間の自動調整はPower Queryで可能ですか?
A: Windowsのタイムゾーン設定が夏時間に対応している場合、Power Queryの「ローカルタイムゾーンに変換」はその情報を利用します。ただし、変換が常に正確とは限らないため、特に重要なデータの場合は手動で夏時間のルールを実装するか、固定のオフセットを使用することを推奨します。

まとめ

Power Queryで日付と時刻がずれる原因は、タイムゾーンの解釈の違いに集約されます。データソースの特性を理解し、Power Queryのデータ型と変換機能を適切に使うことで、多くの問題は解決できます。特に、デフォルトの動作に頼りすぎず、明示的なタイムゾーン指定を心がけることが重要です。組織全体で一貫した設定を維持するためには、管理者と連携してポリシーを確認することも忘れないでください。

📊

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

🏆

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