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

https://learn.microsoft.com/ja-jp/power-bi/connect-data/desktop-data-types

　Power BI DesktopとData Analysis Expressions (DAX) のデータ型について調べます。
　
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/7bf7babe-47c1-8d54-0e03-6b6636715c8e.png)

# 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日にされてしまいます。

```text:日付最大、最小
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 結果が大きすぎるか小さすぎます。
}
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b1a0f35e-6841-ce48-77c9-7ff0fbed1df9.png)

　1900年以前を入力するには **DATE(-1800,1,1)** のようにマイナスの年で記述できます。しかし、これに時間が加わると、少々異なります。 **100年1月1日は時間を入れることができません。** なぜなら、マイナス部分では時間を入れると0時0分より小さな値になってしまうため、"100/1/1 12:00:00"は"100/1/1 00:00:00"にされてしまいます。
![pic.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d8a418f1-5baf-c9b0-e3cd-e848c1ae76c7.png)
　日付／時間で表す場合は、**100年1月2日から有効となります。**
　DAXで1899年12月29日以前の日付で時間を設定する方法は、TIMEをマイナスで記述することになります。

```text: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秒からです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/6446b7fe-1bb8-3035-78c8-9de26e389344.png)
　しかし、DATEDIFFの計算をさせると、100年より前の日付の計算は正しく行われません。

```text: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
```

```text:DAX-DATEDIFF
DateDiff =
DATEDIFF (
    'DateTime'[Value1],
    'DateTime'[Value2],
    DAY
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/71376238-15c1-5d10-cf74-5ff2c457ef2d.png)

　100年以降の日付については計算できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/44d42d96-0439-8e07-8ba6-60f9e2253e02.png)

### Excelの日付システムとの違い
https://learn.microsoft.com/ja-JP/office/troubleshoot/excel/1900-and-1904-date-system

　Excelの日付の扱いは、先に発売されていたLotus123のファイルを読み込めるようにするため、1900年1月1日を1として日付を数える方法をとっています。そして、Lotus123との互換性を重視するため、本来存在しない1900年2月29日が使用できるようになっています。
　下の図では、日付の項目に日付形式で入力した時、日付として認識するかどうか、その場合のシリアル値はいくつになるかを示しています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/7d18838c-7e3e-6d73-de2e-047074b3826b.png)
　1900年1月1日から9999年12月31日までが日付として有効であり、1900年2月29日にもシリアル値が振られています。ちなみに、シリアル値0を日付の書式で表示すると **1900/1/0** と表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/43242e0f-ec3e-98e7-d210-b876deeee78b.png)

　また、ExcelはMicrosoft Windowsではなく、Apple Macintoshで最初にリリースされました。Macintosh版では、うるう年の例外が発生する1900年を避けるため、1904年1月1日を0として数える日付システムを採用しました。Excelでは、この1900年と1904年の2種類の日付システムを切り替えて使える仕組みを持っており、標準では1900年の日付システムになっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/53c6386a-4151-5dc1-e5ce-b8f65aec4dc0.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/1b04a60a-6963-7d7a-2ce6-cf478409ed71.png)

　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では、マイナスのシリアル値を持つことはできません。

```text:日付チェック
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 に変換できません。
    }
)
```

```text:シリアル値計算
Serial = CONVERT(DateTimeCheck[DateTIme], double)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/72672d5c-8a43-dd22-78f7-6daad89bc27a.png)

### Excelからの読み込み 1900年日付システム
　以上のようにExcelとの違いがあるので、読み込み時にどのようになるかを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/d1c2dd8f-ed70-752c-2a93-9650a7b7051a.png)

|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年の日付システムの時と同様に読み込みすることができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/5aeffe83-09ac-dc8b-b432-aa3035852535.png)

|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日になります。
![pic2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/a7828066-938e-1419-40c3-cb1d91751767.png)

## 日付／時刻／タイムゾーン
　モデルに読み込まれる際に日付/時刻に変換されます。

|元データ|読み込まれたデータ|
|:--|:--|
|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](https://qiita.com/spumoni/items/9459635b69db773eca30)
1. [[Power BI] Power Queryの『型』を探る (2) text, temporal falimy](https://qiita.com/spumoni/items/ce38958d9858ea132a8f)
1. [[Power BI] Power Queryの『型』を探る (3) number 蛇足](https://qiita.com/spumoni/items/c46b3ddfb8173b6e9aa2)
1. [[Power BI] Power Queryの『型』を探る (4) null](https://qiita.com/spumoni/items/d8279858ceb627af8101)
1. [[Power BI] Power Queryの『型』を探る (5) カスタム型](https://qiita.com/spumoni/items/ec6893c9e4527b674b76)
1. [Power BIデータモデルの型システム (1) 数値型、日付／時刻型](https://qiita.com/spumoni/items/c73e465b691b15694ba0)
1. [Power BIデータモデルの型システム (2) テキスト型、論理型](https://qiita.com/spumoni/items/476494dc7818edc5e190)