【Excel】Power Queryで日付がシリアル値のまま表示される時の変換方法

Power QueryでExcelやCSVからデータを読み込んだときに、本来なら日付として認識されるべき値が「45292」のようなシリアル値(数値)のまま表示されるトラブルは、多くの会社員が経験するものです。この現象は、Power Queryがデータ型を自動的に推測する際、元データの形式やロケールの違いが原因で発生します。日付として正しく扱えなければ、フィルターやピボットテーブル、集計など後続の処理に支障をきたすため、早急に解決する必要があります。本記事では、シリアル値が表示される根本的な原因を切り分け、Power Queryで確実に日付へ変換する手順を具体的に解説します。

なぜ日付がシリアル値で表示されるのか

Power Queryがデータを読み込むとき、各列のデータ型を自動的に判定します。この判定は先頭の数行のサンプルに基づいて行われるため、日付の表記方法が地域やシステムによって異なると、正しく日付型と認識されずに数値型(シリアル値)として扱われることがあります。シリアル値とは、日付を1900年1月1日からの経過日数で表した数値で、Excelの内部表現と同じものです。たとえば「2024/1/1」はシリアル値「45292」に相当します。Power Queryでは、元データがシリアル値そのものである場合や、日付文字列がロケールに合わない場合に、自動変換が機能せずに数値のまま残ります。

代表的な原因

• 元データがシリアル値として保存されている:Excelのセルに日付を入力しても、内部ではシリアル値として保持されています。CSVなどにエクスポートする際に、書式情報が失われて数値として出力されるケースです。
• ロケールの不一致:Power Queryのリージョン設定と元データの日付表記(月/日/年 か 日/月/年 か)が一致しないと、日付として認識されません。
• データ型の自動検出の限界:先頭行に数値が混ざっているなど、サンプリングが不十分な場合に誤判定が起こります。

Power Queryで日付に変換する基本的な手順

ここでは、最も確実な変換手順を説明します。以下の手順はPower Queryエディタ上で行います。

1. Power Queryエディタを開き、問題の列(シリアル値が表示されている列)を選択します。
2. リボンの「ホーム」タブにある「データ型」のドロップダウンリストから「日付」を選択します。これにより、シリアル値が日付に変換されます。
3. もし変換に失敗する場合は、列のデータ型アイコンが「123」(数値)であることを確認し、右クリックメニューから「データ型の変更」→「日付」を選びます。
4. ロケールが原因で変換できない場合、「列の追加」タブの「日付」グループにある「ロケールから日付」をクリックします。ダイアログで元データのロケール(例:日本語)と日付形式(例:2024/1/1)を指定します。
5. 変換後、プレビューで日付に変わったことを確認し、「ホーム」タブの「閉じて読み込む」でExcelに反映します。

上記の手順でほとんどのケースは解決します。ただし、元データが様々な形式を含む場合は、次の応用的な方法を試してください。

変換に失敗するケースとその対処法

基本手順でうまくいかない場合、以下のような理由が考えられます。状況別の対処法を比較表にまとめました。

元データの例	原因	対処法
45292	シリアル値のまま取り込まれた	列のデータ型を「日付」に変更するだけで変換されます。
1/2/2024(米国形式)	ロケールが日本語(年/月/日)と異なる	「ロケールから日付」機能で元データのロケールを指定します。
2024/1/2 10:30	日時形式だが日付のみ欲しい	「変換」タブの「日付のみ」を使用して時刻部分を削除します。
2024/01/02(文字列)	テキストとして認識されている	データ型を直接「日付」に変更するか、「解析」→「日付」を使用します。

より複雑な変換が必要な場合

カスタム関数を使用して、任意の形式を日付に変換することも可能です。たとえば、シリアル値がテキストとして保存されている場合は、まず数値に変換してから日付型に変更します。「列の追加」タブの「カスタム列」で、Date.From(Number.From([列名])) のようにM言語の関数を利用できます。また、複数の形式が混在する列では、条件分岐を使って変換する必要があります。

日付変換に便利な関数とカスタム列の活用

Power Queryには日付変換に特化した関数がいくつか用意されています。代表的なものを紹介します。

主な関数

• Date.From:数値またはテキストから日付を生成します。シリアル値の数値から変換する場合に使います。
• Date.FromText:テキスト文字列を日付に変換します。ロケールを指定できるオプションがあります。
• Number.From:日付をシリアル値に戻す際に使用します。

カスタム列の作成例

例えば、列「日付シリアル」が数値として存在する場合、新しい列を追加して「= Date.From([日付シリアル])」と入力します。これでシリアル値から日付列が生成されます。元の列を置き換える場合は、「変換」タブの「値の置換」ではなく、「列の追加」→「カスタム列」で新しい列を作成し、後で不要な列を削除する方法が安全です。

管理者への確認事項と組織設定の影響

企業環境では、Power Queryの動作に影響を与える可能性のある設定がいくつかあります。以下の点を管理者に確認しておくと、トラブルシューティングがスムーズになります。

• 地域設定:Windowsの地域設定やExcelの「詳細オプション」で日付の区切り文字や順序が変更されている場合、Power Queryのデフォルトロケールも変わることがあります。
• グループポリシー:組織のセキュリティポリシーにより、Power Queryの一部機能(カスタム関数の実行など)が制限されている場合があります。
• データソースの権限:共有フォルダやSharePoint上のファイルを読み込む際に、接続設定が影響する場合があります。

これらの設定を変更する必要がある場合は、管理者に依頼してください。自分で変更してしまうと、他のユーザーや業務に影響が出る可能性があります。

よくある質問(FAQ)

Q. なぜシリアル値で表示されるのですか?

A. 元データが数値として保存されているか、Power Queryの自動型変換が正しく機能しなかったためです。列のデータ型を手動で日付に変更することで解消します。

Q. 一度変換した後に、元のシリアル値に戻す方法はありますか?

A. 変換後の日付列を選択し、データ型を「数値」に変更するとシリアル値に戻ります。ただし、元データのソースを変更しない限り、Power Queryのクエリを更新すれば再変換されます。

Q. データ更新のたびに同じ変換が必要ですか?

A. いいえ。Power Queryのクエリで変換手順が保存されるため、一度設定すれば以降のデータ更新時に自動的に適用されます。

Q. 複数の列を一度に日付に変換できますか?

A. 複数列を選択した状態でデータ型を変更すれば、一度に変換できます。ただし、列ごとに元の形式が異なる場合は注意が必要です。

まとめ

Power Queryで日付がシリアル値で表示される問題は、データ型の手動変更やロケール指定によって簡単に解決できます。まずは列のデータ型アイコンを確認し、必要に応じて「ロケールから日付」機能を使いましょう。元データの形式が複雑な場合は、カスタム関数やM言語を活用することで柔軟に対応できます。組織の設定が影響している場合には、管理者と連携して適切な環境を整えることが重要です。これらの方法を身につければ、日付データの取り扱いに関する日々のストレスから解放されるでしょう。

📊

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

🏆

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