【Excel】Power Queryで日付が勝手に変わる時の型変換確認手順

ExcelのPower Queryを使ってデータを取り込むと、日付列の値が意図しない形式に変わってしまうことがあります。たとえば「2024/4/1」が「2024/01/04」になったり、シリアル値に変わったりするケースです。この問題は、Power Queryがデータの型を自動的に判断する仕組みと、ロケールや地域設定の違いによって発生します。本記事では、日付が勝手に変わる原因を整理し、型変換を正しく確認・修正する手順を解説します。

Power Queryで日付が勝手に変わる代表的な原因

日付が勝手に変換される原因は、大きく三つに分類できます。ひとつは、Power Queryがデータを読み込む際に「ロケール」を正しく認識しないことです。ロケールとは、日付の表記方法(月日順や区切り文字など)を決める地域設定です。日本のロケールでは「年/月/日」が標準ですが、英語のロケールでは「月/日/年」として解釈されるため、日と月が入れ替わることがあります。

次に、列のデータ型が「テキスト」ではなく「日付」や「日時」に自動設定される際に、元の文字列の書式と一致しない場合です。たとえば「2024/4/1」という文字列を「日付型」として読み込むと、シリアル値に変換され、後から書式を変更しても見た目が変わらないことがあります。

三つ目は、CSVやデータベースからの取り込み時に、システムの言語設定や地域設定が影響するケースです。これらの設定はPower Queryの「オプション」から変更できますが、変更が反映されない場合もあります。

型変換の確認手順(基本編)

まずは、Power Queryエディタでデータ型がどのように設定されているかを確認します。以下の手順に沿って操作してください。

1. Excelで「データ」タブを開き、「テーブルまたは範囲から」または「CSVから」などでデータを読み込み、Power Queryエディタを起動します。
2. 対象の日付列を選択します。列ヘッダーの左側に小さなアイコン(「123」や「ABC」、「日付」など)が表示されます。このアイコンが現在のデータ型を示しています。
3. アイコンが「日付」または「日時」になっているか確認します。もし「テキスト」になっている場合は、日付として認識されていない可能性があります。
4. 列ヘッダーのアイコンをクリックすると、データ型の変更メニューが表示されます。「日付」または「日時」を選択して変更します。
5. 変更後、プレビュー画面で実際の値がどのように表示されるか確認します。たとえば「2024/4/1」が「2024-04-01」と表示されるのが正しいです。
6. Power Queryエディタの「ファイル」メニューから「閉じて読み込む」をクリックし、Excelシートに反映します。

データ型アイコンが変わらない場合の対処

データ型を変更してもアイコンが変わらない、またはエラーが表示される場合は、元のデータに誤りがあるか、ロケールが正しく設定されていない可能性があります。次の詳細手順に進みます。

型変換の確認手順(詳細編:ロケールとデータ型)

基本手順で解決しない場合、ロケール設定を明示的に指定する方法を試します。Power Queryでは、列レベルでロケールを指定できる機能があります。

1. Power Queryエディタで、日付列を右クリックし、「データ型の変更」→「ロケールを使用して…」を選択します。
2. 表示されたダイアログで、「データ型」として「日付」を選び、「ロケール」に「日本語(日本)」を選択します。
3. 「変換前の書式」オプションで、元の文字列の書式を指定します。たとえば「2024/4/1」なら「yyyy/M/d」と入力します。書式コードはカスタムフォーマットに従います。
4. 「OK」をクリックして変換を実行します。プレビューで値が正しい日付に変わっていることを確認します。
5. もし変換に失敗する場合は、「変換前の書式」を変更して再度試します。よくあるパターンとして、「yyyy-MM-dd」や「dd/MM/yyyy」などがあります。

カルチャ設定の変更

Power Query全体のロケール(カルチャ)を変更するには、「ファイル」→「オプションと設定」→「クエリオプション」を開き、「地域設定」の「カルチャ」を「日本語(日本)」に設定します。これにより、新しく読み込むクエリのデフォルトロケールが変わります。

状況別:よくある変換パターンと対処方法

実際によく発生する変換のパターンを表にまとめました。自身のデータがどのパターンに該当するかを確認し、適切な対処を行ってください。

元のデータ例	Power Queryの解釈	変換結果	対処方法
2024/04/01	日付型(ロケール不明)	2024-04-01(正しい)	特に問題ありません。ただしロケールを日本語に固定すると安全です。
04/01/2024	日付型(英語ロケール)	2024-04-01(月=4、日=1)	ロケールを英語(米国)に設定して読み込むか、テキストとして読み込み後パース。
2024/4/1	日付型(自動)	2024-04-01(正しい)	通常は問題ありません。
1-Apr-2024	テキスト型	そのまま文字列	「ロケールを使用」で「英語(米国)」を指定し、書式「d-MMM-yyyy」で変換。
20240401	数値型	20,240,401	テキストに変換後、書式「yyyyMMdd」で日付型に変換。

失敗パターンと注意点

型変換でよくある失敗パターンを紹介します。これらを事前に把握しておくことで、無駄な試行錯誤を減らせます。

日付と時刻が混在する場合

「2024/04/01 10:30:00」のようなデータは「日時型」として扱われます。これに対して「日付型」に変換しようとすると、時刻部分が切り捨てられます。注意点として、元のデータに時刻情報が含まれていることを意識し、必要に応じて「日付」または「日時」を選択します。

nullや空文字が含まれる場合

日付列にnullや空文字が混ざっていると、型変換時にエラーになることがあります。Power Queryでは、nullの行は変換後もnullのまま残りますが、空文字は変換できずエラーになります。事前に「列の置換」で空文字をnullに変換するか、条件列を使って処理します。

シリアル値として認識される場合

ExcelからPower Queryに読み込んだとき、日付がシリアル値(数値)になっていることがあります。これは、元のExcelシートで日付が文字列ではなく数値として保存されている場合です。この場合は、数値型のまま保持するか、Power Queryで「日付」型に変換する必要があります。変換後は、書式設定で表示形式を変更してください。

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

会社のPCでPower Queryを使用する場合、以下の設定が管理者側で制限されていることがあります。トラブルの切り分けのために、管理者へ確認する内容をまとめました。

• Windowsの地域設定: 「コントロールパネル」→「時計と地域」→「地域」で、形式が「日本語(日本)」になっているか確認します。Power Queryはこの設定を参照することがあります。
• Excelのオプション: 「ファイル」→「オプション」→「詳細設定」で、「1904年から計算する」のチェックが入っていないか確認します。この設定がオンだと日付のシリアル値が異なります。
• Power Queryのカルチャ設定: 「クエリオプション」→「地域設定」で、カルチャが「日本語(日本)」になっているか確認します。グループポリシーで固定されている場合もあります。
• データソースのエンコード: CSVファイルの文字コード(UTF-8, Shift-JISなど)が正しいか確認します。文字化けが原因で日付が正しく認識されないことがあります。

これらの設定変更ができない場合、「ロケールを使用して…」の機能で個別に変換する方法が有効です。

よくある質問(FAQ)

Q: 日付が「42370」のような数字に変わってしまいました。どうすれば戻せますか?

A: それはシリアル値です。Power Queryエディタで該当列のデータ型を「日付」に変更すれば、正しい日付に変換されます。Excelシート上で表示形式を変えても解決しない場合は、Power Queryで再変換してください。

Q: Power Queryで日付型に変換したのに、Excelに読み込むとシリアル値で表示されます。

A: Excel側のセルの書式設定が「標準」になっている可能性があります。セルを選択し、「ホーム」タブの「数値の書式」から「日付」を選択してください。もしくは、Power Queryで「日付」型に変換した後、Excelに読み込む前に「書式の指定」ステップを追加することもできます。

Q: 毎回同じ設定を繰り返すのが面倒です。テンプレート化できますか?

A: 可能です。Power Queryで作成したクエリを「接続のみ」として保存し、新しいデータに対して「パラメーター」を使って読み込むことができます。または、クエリをコピーしてデータソースを差し替える方法もあります。

まとめ

Power Queryで日付が勝手に変わる問題は、データ型とロケール設定を正しく確認することで大部分が解決できます。まずは列のデータ型アイコンを確認し、必要に応じて「ロケールを使用して…」で明示的に変換することが重要です。また、元データの書式とPower Queryの解釈が一致しているかを常に意識しましょう。会社のPCで設定が制限されている場合は、クエリごとにロケールを指定する方法が有効です。

📊

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

🏆

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