Power BIデータモデルの型システム (1) 数値型、日付／時刻型

　Power BI DesktopとData Analysis Expressions (DAX) のデータ型について調べます。
　

1.数値型

　Power BIデータモデルでは、3つの数値型サポートされています。

DOUBLE(10進数)

　64ビット浮動小数点です。

CURRENCY(固定小数点数)

　小数点以下は常に4桁で固定され、有効数字は19桁で、丸め処理で発生する誤差が切り捨てられます。最大値は922,337,203,685,477.5807です。

INTEGER(整数)

　64ビットの整数値です。ビジュアルで表現できる最大の数は、JavaScriptの制限を受けます。

2.DATETIME(日付／時刻型)

　日付/時刻/タイムゾーンと期間は、モデルに読み込まれる時点で変換されます。 Power BI Desktop データ モデルでは、日付/時刻のみがサポートされていますが、日付または時刻として個別に書式設定できます。

最大日付と最小日付

　Docsには、Power BIデータモデルで扱える日付は1900年から9999年までと書かれています。DAX式を書いて確認をしてみると、最大と最小の日付は以下のように、100年1月1日から9999年12月31日までが使えるようです。100年より小さい日付は、100年1月1日にされてしまいます。

日付最大、最小

DateTimeTest = 
{
    ( 1,"Under", DATE(-1801, 12, 31) ),  // Bad 0100/01/01
    ( 2,"MIN", DATE(-1800, 1, 1) ),
    ( 3,"MAX", DATE(9999,12,31) )
    //( 4,"Over", DATE(10000, 1, 1) ) // Error 結果が大きすぎるか小さすぎます。
}

　1900年以前を入力するには DATE(-1800,1,1) のようにマイナスの年で記述できます。しかし、これに時間が加わると、少々異なります。 100年1月1日は時間を入れることができません。 なぜなら、マイナス部分では時間を入れると0時0分より小さな値になってしまうため、"100/1/1 12:00:00"は"100/1/1 00:00:00"にされてしまいます。

　日付／時間で表す場合は、100年1月2日から有効となります。
　DAXで1899年12月29日以前の日付で時間を設定する方法は、TIMEをマイナスで記述することになります。

1899年12月30日以前の時刻の設定

DATE(-1, 12, 30) + TIME(6, 10, 0)    // 1899/12/30以降　　1899/12/30 06:10:00
DATE(-1, 12, 29) - TIME(6, 10, 0)    // 1899/12/29以前　　1899/12/29 06:10:00

Power Queryからの日付／時刻

　一方、Power Queryから読み込み表示することができるのは、1年1月1日0時0分0秒からです。

　しかし、DATEDIFFの計算をさせると、100年より前の日付の計算は正しく行われません。

PowerQuery

let
    Source = Table.FromRecords(
        {[
            Kind = "Date",     
            Value1 = #datetime(99,1,1,0,0,0), 
            Value2 = #datetime(100,1,1,0,0,0)
        ]}
    )
in
    Source

DAX-DATEDIFF

DateDiff =
DATEDIFF (
    'DateTime'[Value1],
    'DateTime'[Value2],
    DAY
)

　100年以降の日付については計算できます。

Excelの日付システムとの違い

　Excelの日付の扱いは、先に発売されていたLotus123のファイルを読み込めるようにするため、1900年1月1日を1として日付を数える方法をとっています。そして、Lotus123との互換性を重視するため、本来存在しない1900年2月29日が使用できるようになっています。
　下の図では、日付の項目に日付形式で入力した時、日付として認識するかどうか、その場合のシリアル値はいくつになるかを示しています。

　1900年1月1日から9999年12月31日までが日付として有効であり、1900年2月29日にもシリアル値が振られています。ちなみに、シリアル値0を日付の書式で表示すると 1900/1/0 と表示されます。

　また、ExcelはMicrosoft Windowsではなく、Apple Macintoshで最初にリリースされました。Macintosh版では、うるう年の例外が発生する1900年を避けるため、1904年1月1日を0として数える日付システムを採用しました。Excelでは、この1900年と1904年の2種類の日付システムを切り替えて使える仕組みを持っており、標準では1900年の日付システムになっています。

　Power BIデータモデルでは、1900年の日付システムを使用していますが、1900年2月29日の問題を解決するために、1899年12月30日をシリアル値0として日付をカウントするようにしています。つまり、開始日を1日繰り上げし、シリアル値1を1899/12/31に設定 し、1900年2月29日はエラーにしてシリアル値を振らないことで辻褄を合わせました。Lotus123とのずれは、1900年1月1日から1900年2月28日までの期間発生します。
　また、100年1月1日から1899年12月29日までの日付をマイナスのシリアル値で持てるようにしています。Excelでは、マイナスのシリアル値を持つことはできません。

日付チェック

DateTimeCheck = DATATABLE(
    "No", INTEGER,
    "DateTIme", DATETIME,
    {
        { 1, "0099/12/31" },    // Bad 年賀1999年に変換されてしまう
        { 2, "100/1/1" },
        { 3, "1899/12/30" },
        { 4, "900/1/1 00:00:00" },
        { 5, "1900/2/28 00:00:00" },
        //{ 6, "1900/2/29" },   // Error '1900/2/29' を型 Date に変換できません。
        { 7, "1900/3/1 00:00:00" },
        { 8, "1998/7/5 00:00:00" },
        { 9, "9999/12/31 23:59:59" }
        //{ 10, "10000/1/1" }  // Error '10000/1/1' を型 Date に変換できません。
    }
)

シリアル値計算

Serial = CONVERT(DateTimeCheck[DateTIme], double)

Excelからの読み込み 1900年日付システム

　以上のようにExcelとの違いがあるので、読み込み時にどのようになるかを確認します。

No	日付	結果	処理内容
1	0099/01/01	✕	エクセルからは文字列で入ってきて正しい日付に変換できない
2	1900/01/01	〇	エクセルからは文字列で入ってくるが日付に変換可能
3	1900/01/01	〇	Good!
4	1900/02/28	〇	Good!
5	1900/02/29	✕	1900/3/1に書き換えられる
6	1900/03/01	〇	Good!
7	1998/07/05	〇	Good!
8	9999/12/31	〇	Good!
9	10000/01/01	✕	Power BIデータモデルで日付型に変換時にエラーとなる

Excelからの読み込み 1904年日付システム

　Excelのブックがが1904年の日付システムで設定されていても、Power BIは1900年の日付システムの時と同様に読み込みすることができます。

No	日付	結果	処理内容
1	0099/01/01	✕	エクセルからは文字列で入ってきて正しい日付に変換できない
2	0100/01/01	〇	エクセルからは文字列で入ってくるが日付に変換可能
3	1900/01/01	〇	エクセルからは文字列で入ってくるが日付に変換可能
4	1904/01/01	〇	Good!
5	1998/07/05	〇	Good!
6	9999/12/31	〇	Good!
7	10000/01/01	✕	Power BIデータモデルで日付型に変換時にエラーとなる

日付／時刻

　DOUBLE(10進数)型で保存されます。日付部分は1899年12月30日を0とする日数で表されます。上記に書いた通り、100年1月2日から9999年12月31日までを表現できます。時間部分は、1/300秒の整数倍ので保存されます。
※公式ドキュメントでは、「1900 年から 9999 年までの日付がサポート」されているとなっています。

日付

　日付/時刻値がモデルに変換される際、時刻を表す小数点以下は0になります。日付だけであれば、100年1月1日から9999年12月31日までを表現できます。

時刻

　 時刻値は 日付/時刻値の小数部分で保存されます。日付部分は0になります。したがって、時刻値を日付／時刻に変換すると、日付は1899年12月30日になります。

日付／時刻／タイムゾーン

　モデルに読み込まれる際に日付/時刻に変換されます。

元データ	読み込まれたデータ
1964/01/02 06:00:00 +09:00	1964/01/02 06:00:00
1964/01/02 06:00:00 +02:00	1964/01/02 06:00:00
1964/01/02 06:00:00	1964/01/02 06:00:00

期間

　モデルに読み込まれる際に 10 進数型に変換されます。

元データ	読み込まれたデータ
1:00:00:00	1.0
0:12:00:00	0.5
0:12:30:00	0.5208333333333334

関連記事

1. [Power BI] Power Queryの『型』を探る (1) number
2. [Power BI] Power Queryの『型』を探る (2) text, temporal falimy
3. [Power BI] Power Queryの『型』を探る (3) number 蛇足
4. [Power BI] Power Queryの『型』を探る (4) null
5. [Power BI] Power Queryの『型』を探る (5) カスタム型
6. Power BIデータモデルの型システム (1) 数値型、日付／時刻型
7. Power BIデータモデルの型システム (2) テキスト型、論理型