PowerQueryからExcelにデータを読み込む際の日付が正常に表示されない場合の対応

最近、社内で月次のレポートを作成する際に表題の事象に遭遇しましたが、無事解決できましたので解決方法について備忘録を兼ねて記載いたします。
クエリの参照元はSharePoint Online上にあるカスタムリストで、カスタム側では随時情報が更新されているのですが、こちらの情報の中に日付情報があり、こちらをPower Queryで整形しExcelに読み込む際に表示が変わってしまうという事象です。
具体的にやったことは以下になります。

Power Query上の列のデータ型の修正

まずはPower Queryで参照元のデータを読み込みリストを表示します。
カスタムリストがあるサイトのURLを入力します（カスタムリスト自体は次のステップで選択します）
「データの変換」ボタンを押下
PowerQuery上でリストが展開されます。
このままPowerQuery左上の「閉じて読み込む」ボタンを押すとExcelに表示することができます。

しかし、この状態では日付が正常に表示できず、以下のような数列の表示になってしまいます。
前項で「閉じて読み込む」で既にExcelに表示してしまった場合は、Excel上部リボンの「データ」➡「クエリと接続」を開き、右側に表示されるカラムに先ほどPowerQueryで開いたクエリが表示されていますので、これを右クリックし「編集」を押下します。

ポイント：SharePointの日時列はUTCで格納されている場合が多く、Power Queryでそのまま読み込むと「Excelの日付シリアル（数値）」や、タイムゾーンずれで意図しない日付になることがあります。
まずは該当列のデータ型を「日付/日時」に正しく設定し、必要に応じてロケール指定（「データ型の変更 > ロケールを使用」）を行います。

列を選択 → 右クリック → データ型の変更 → ロケールを使用して… → 種類：日付（または日付/時刻）、ロケール：日本語（日本） を指定。
この段階でうまく解釈できない値（空白・テキスト・数値混在）がある場合は、次のステップで条件式を使って正規化します。

ステップ２

列の条件式を修正（混在データを正規化／UTC→JSTへ補正／空白はエラーにせず空欄で残す）

日付列に「数値（Excelシリアル）」「テキスト（yyyy/mm/dd など）」「日時（UTC）」が混在していると、型変換時にエラーが出たり、JSTで1日ずれたりします。
以下のように カスタム列 を追加し、入力の型に応じて分岐変換するのが確実です（列名を DueDate と仮定）。

Power Queryエディター → リボン「列の追加」→「カスタム列」。
新しい列名を DueDate_Normalized にし、次の式を貼り付けます（JST＝UTC+9に補正）。

let
    toDate =
        (v as any) as nullable date =>
            if v = null then
                null
            else if Type.Is(Value.Type(v), Number.Type) then
                // Excelシリアル → 1899/12/30起点で日付化
                Date.From(Date.AddDays(#date(1899, 12, 30), Number.From(v)))
            else if Type.Is(Value.Type(v), type datetimezone) then
                // すでに日時(タイムゾーン付き) → JST(+9)へ → 日付へ
                Date.From(DateTimeZone.SwitchZone(v, 9))
            else if Type.Is(Value.Type(v), type datetime) then
                // タイムゾーンなし日時 → 一旦UTCとして扱いJSTへ → 日付へ
                Date.From(DateTimeZone.SwitchZone(DateTimeZone.From(v), 9))
            else
                // テキストなどはロケールを日本にして解釈
                try Date.FromText(Text.From(v), "ja-JP") otherwise null
in
    Table.AddColumn(
        #"前のステップ名をここに",           // ← 直前のステップ名に置き換えてください
        "DueDate_Normalized",
        each toDate([DueDate]),
        type date
    )

※ 「前のステップ名」は、ご自身のクエリの直前のステップ名に置き換えてください（例：#"Changed Type" など）。
※ Date.FromText にロケール "ja-JP" を渡すことで、yyyy/mm/dd や yyyy-mm-dd など日本語環境の表記を安定して解釈できます。
※ SharePointの日時列（UTC）をローカル時間で日付化したい場合は、必ず DateTimeZone.SwitchZone(..., 9) のようにタイムゾーン補正を入れてから Date.From で日付へ落とすのがポイントです。

追加後、元の DueDate 列は残し、DueDate_Normalized をレポート用に使用します（慣れるまでは元列の退避が安心）。
問題がなければ、DueDate を削除し、DueDate_Normalized を 右クリック → 名前の変更 で DueDate にリネームしてもOKです。

別解（GUI中心の手早い方法）
単純に「UTCの日時 → JSTの日付」にしたいだけなら、該当列を選択し、
① データ型「日付/時刻/タイムゾーン」→ ② 変換「タイムゾーンの切り替え」で +9 → ③ データ型「日付」
の順で操作しても同様の結果が得られます。

ステップ３

Excelに読み込み（表示崩れを防ぐ最終チェック）

Power Queryエディターで、日付列のアイコンが 「日付」（カレンダー）になっていることを確認します。
もし「123（数値）」のアイコンなら、データ型が数値のままです。必ず日付に直します。
左上の 「閉じて読み込む」 でExcelへ出力します。
Excel上で、念のため対象列の表示形式が日付になっているか確認します（必要に応じて「短い日付」「ユーザー設定」等）。
ここで数値に見える場合は、Power Query側のデータ型が数値のままになっている可能性が高いので、戻って再確認してください。

まとめ

SharePoint発の日時はUTCで来ることが多く、タイムゾーン補正とデータ型の厳密化（ロケール指定含む）が肝です。
具体的には、① データ型を「日付/時刻（必要ならTZ付）」に合わせる → ② 分岐式で混在値を正規化（UTC→JST、数値→日付、テキスト→ロケール解釈） → ③ Excel読み込み前に日付型アイコンの確認の順で進めると、表示崩れや1日ズレを防げます。
今回のケースでは、DateTimeZone.SwitchZone(..., 9) と Date.From の組み合わせ、および Date.FromText(..., "ja-JP") によるロケール解釈で安定化しました。

補足：Excel上で見えている「シリアル値」は、1899/12/30を起点にした経過日数です。Power Query内で数値→日付へ正規化すれば、読み込み時点で正しい日付表示に統一できます。

PowerQueryからExcelにデータを読み込む際の日付が正常に表示されない場合の対応

PowerQueryからExcelにデータを読み込む際の日付が正常に表示されない場合の対応

目次

Power Query上の列のデータ型の修正

ステップ２

ステップ３

まとめ

コメント