[Power BI] Excelシリアル値日付データの変換方法

2023年12月9日2024年5月3日

このページでは、Excel内で定義されているシリアル値の日付データをPower BI内で変換し、データ型も含めて日付データとして取り扱う方法を紹介します。

シリアル値の日付データとは？(Excel)

シリアル値とはExcel内で日付データを計算するための数値のことです。
1900年1月1日を”1”とし、日ごとに1づつ加算されています。
2023年1月1日は”44927″になります。

Excel内のショートカットキーで日付とシリアル値の変換ができます。
　日付　　　→　シリアル値：”Ctrl” + “Shift” + “~”
シリアル値　→　日付　　　：”Ctrl” + “Shift” + “#”

ごくまれに、Excel内に記載された日付データをPower BIで読み込んだ場合、シリアル値で取り込まれてしまうことがあります。

Power BIでの変換方法

調べてみた限り、Power BIの設定などでシリアル値から日付データに直接変換する方法はないみたいです。

変換したいデータ

下記のように、Excelから読み込んだ日付データがシリアル値になっていたと仮定します。

ダミーデータとして、2022年12月9日(44904)から2023年12月10日(45270)の連続した日付データです。

変換のための処理

下記の手順のように、シリアル値と日付の新規テーブルを作成し、日付データを参照することで変換します。

1.変換のためにシリアル値と日付が記載された新しいテーブルを作成します。

モデリングタブまたはテーブルツールタブの新しいテーブルから、下記のDAX式を入力します。

カレンダー_シリアル値 = 
    ADDCOLUMNS(
        CALENDAR("2022/12/9","2023/12/10"),
        "シリアル値",DATEDIFF("1900/1/1",[Date],DAY)+2
    )

ADDCOUMNS関数を使用してテーブルを作成します。
日付に対するシリアル値の計算はDATEDIFF関数を使用しています。

=DATEDIFF(<基準とする日付>, <計算したい日付>, <間隔>)
DATEDIFF関数は２つの日付の間隔を計算する関数です。

DATEDIFFの戻り値に+2している理由
Excelでのシリアル値は、存在しないはずの日付1900年2月29日をシリアル値”60″として管理しています。
そのため、DATEDIFFを使用して、1900年3月以降を計算する場合はシリアル値を1ずらす必要があります。

2.作成したテーブルと変換したいデータのリレーションシップを作成します。

Excelからインポートしたシリアル値と、DATEDIFF関数で作成したシリアル値でリレーションシップ。
インポートしたデータによっては、多対一(*:1)になるかもしれません。

3.RELATED関数を使用して、日付データを参照

Excelからインポートしたデータに新しい列を作成し、RELATED関数を使用して日付データを参照します。
インポートしたデータを選択し、モデリングタブまたはテーブルツールタブから新しい列をクリック。
下記のDAX式を入力します。

日付データ = RELATED('カレンダー_シリアル値'[Date])

=RELATED(<参照する列>)
RELATED関数はリレーションシップがあるテーブルの列データを戻り値として返します。

列作成後は、列ツールの書式から、適切な書式に変更してください。今回はShort Dateにしました。

一言で表すと、シリアル値と日付データのあるテーブルからシリアル値とをキーとしてRELATED関数で日付データを参照しています。回りくどい方法ですが、他にもっと簡単な方法があるんですかね？
(日付に対するシリアル値を愚直に計算することも検討しましたが、1900/2/29問題やうるう年の対応が面倒そうだったので、あきらめました。)