このページでは、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関数は２つの日付の間隔を計算する関数です。
2.作成したテーブルと変換したいデータのリレーションシップを作成します。
Excelからインポートしたシリアル値と、DATEDIFF関数で作成したシリアル値でリレーションシップ。
インポートしたデータによっては、多対一(*:1)になるかもしれません。
3.RELATED関数を使用して、日付データを参照
Excelからインポートしたデータに新しい列を作成し、RELATED関数を使用して日付データを参照します。
インポートしたデータを選択し、モデリングタブまたはテーブルツールタブから新しい列をクリック。
下記のDAX式を入力します。
日付データ = RELATED('カレンダー_シリアル値'[Date]) 
=RELATED(<参照する列>)
RELATED関数はリレーションシップがあるテーブルの列データを戻り値として返します。
列作成後は、列ツールの書式から、適切な書式に変更してください。今回はShort Dateにしました。
一言で表すと、シリアル値と日付データのあるテーブルからシリアル値とをキーとしてRELATED関数で日付データを参照しています。回りくどい方法ですが、他にもっと簡単な方法があるんですかね？
(日付に対するシリアル値を愚直に計算することも検討しましたが、1900/2/29問題やうるう年の対応が面倒そうだったので、あきらめました。)
まとめ
- Power BI内でシリアル値の日付データを直接変換する方法はなさそう。
- 日付データからシリアル値を計算するにはDATEDIFF関数を使用。
- リレーションシップを作成後、RELATED関数で日付データを参照することで、日付データを作成。
