[Power BI] Power Queryの『型』を探る (2) text, temporal falimy

（この記事は2022年10月22日の「[PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #19](https://powerbi.connpass.com/event/261515/)」で使用したものです）

　プリミティブ型(Primitive types)はプリミティブ値 (binary、date、datetime、datetimezone、duration、list、logical、null、number、record、text、time、type) を分類するものであり、また、これにはさまざまな抽象型 (function、table、any、none) が含まれます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/5712a27e-b259-53f4-c171-ed5605a13811.png)

https://docs.microsoft.com/ja-jp/powerquery-m/m-spec-types

# 1. text （テキスト型）

https://bengribaudo.com/blog/2018/06/26/4470/power-query-m-primer-part6-types-intro-text

　Power Query で文字を扱う型は text 型のみです。type claim nameには Character.Type もありますが、Text.Type と同等です。

```text:二重引用符
"He said ""run"" and so I did." //  -> He said "run" and so I did.
```

```text:改行
"Hello
World"
```

```text:エスケープシーケンスを使ってエンコード
"Hello#(lf)World"     // UNIX形式改行
"Hello#(cr,lf)World"  // Windows形式改行
"Hello#(tab)World"  // タブ挿入
``` 

```text:16進数を使用してUnicodeを挿入
"Hello #(4e16,754c)" // Hello 世界
```

```text:Escape Escapeで#を表示
"Part Code #(#)(2501)"  // Part Code #(2501)
```

```text:比較
"a" = "a" // true
"a" > "b" // false
Compare.Equals(Comparer.OrdinalIgnoreCase, "A", "a")  // true
```

```text:結合
"Good" & " " & "Morning!"  // Good Morning!
"残り" & 5 & "です。"       // Expression.Error: 演算子 & を型　Text　と　Number　の間に適用できません。
"残り" &  Number.ToText(0.5, "P") & "です。"  // 残り 50.00% です。
"Somthing Profound" & null // null: null との結合は null になる
```

　nullについては回を改めて書きますが、**文字列はnullと結合するとnullになる**ことは覚えておいてください。よくハマるポイントになります。

### Number.ToText

https://qiita.com/spumoni/items/8c4ca046bc511e9a64aa

### Text.Format

https://qiita.com/spumoni/items/81a645de012fd0fed3ba

### 比較関数

https://qiita.com/spumoni/items/cbba8e710620ad5320bd#3%E5%A4%A7%E6%96%87%E5%AD%97%E5%B0%8F%E6%96%87%E5%AD%97%E3%81%AE%E5%8C%BA%E5%88%A5%E3%82%92%E3%81%97%E3%81%AA%E3%81%84%E6%96%B9%E6%B3%95-ci

# 2. Temporal Family （時間に関連する型の仲間）

https://bengribaudo.com/blog/2018/08/15/4557/power-query-m-primer-part8-types-the-temporal-family

```text:date
#date(2018,4,27) // 2018年4月27日
#date(-25,6,10)  // 不可　紀元前の年は表せない
```

```text:time
#time(11,15,25)  // 11時15分25秒
#time(13,0,0.53257)  // 13:00:00.5325700
#time(0,0,0)   // 0:00:00
#time(24,0,0)  // 0:00:00
#time(24,0,1)  // Expression.Error: 結果値が値の許容範囲に含まれていない
```
　#time(0,0,0)と#time(24,0,0)は同じ 0:00:00 を指しますが、全く同じではありません。

```text:datetime,datetimezone
#datetime(2018, 4, 30, 15, 30, 15)  // 2018/4/30 15:30:15
#datetimezone(2018, 4, 30, 15, 30 ,15, 04, 00)  // 2018/4/30 15:30:15 +04:00
```

　durationの引数にはマイナスを使用することもできます。
```text:duration
#duration(2,6,5,10)  // 2d, 6h, 5m, 10s
#duration(-250,-12,-11,-5.5) // -(250d, 12h, 11m, 5.5s)
#duration(0,0,240,0) = #duration(0,4,0,0)
#duration(0,1,-5,0)  = #duration(0,0,55,0)
```

```
#date(2000,1,1) + 3   // Error
#date(2000,1,1) + #duration(1,0,0,0) * 3  // 2000/1/4
```

```text:変換
DateTime.From(#date(2019,12,31))  = #datetime(2019,12,31,0,0,0)
Date.From(#datetime(1000,10,15,13,25,12)) = #date(1000,10,15)
Time.From(#datetime(1000,10,15,13,25,12)) = #time(13,25,12)
DateTimeZone.From(#date(2019, 12, 31)) //  2019/12/31 0:0:0 with system’s current timezone
DateTimeZone.From(#datetime(1000, 10, 15, 13, 25, 12)) // 1000/10/15 13:25:12 with system’s current timezone
```
　datetimezoneへの変換で、タイムゾーンのオフセットが含まれない場合は、ローカルのタイムゾーンが設定されます。


```text:文字列
Date.ToText(#date(2010, 12, 31), "m", "ja-JP") // 12月31日
Date.ToText(#date(2010, 12, 31), "o")          // 2010-12-31T00:00:00.0000000
Date.ToText(#date(2010, 12, 31), "yyyy/MM/dd") // 2010/12/31
```

```text:組み合わせ
#date(2018,4,30) & #time(15,30,10) = #datetime(2018,4,30,15,30,10)
```

## Excelの日時シリアル値 OADate（OLEオートメーション日付）
　Excelでは、日時をシリアル値で扱います。Power QueryでOADateのシリアル値を使うことができます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/1789b5cc-75aa-cd9e-c669-230e6bf24a5a.png)

```text:OADate
Date.From(44562)             // 2022/01/01
Time.From(0.5)               // 12:00:00
Time.From(0.20903935185185)  // 5:01:01
DateTime.From(44562.25)      // 2022/01/01 6:00:00
```
　時間については、有効桁数が足りない場合、期待する時間とずれてしまう可能性があります。

```text:OADate
Time.From(0.209039351851)   // 05:01:00.9999999
```
　Number.Fromを使用して OADate値に変換することができます。

```text:NumberFromOADate
Number.From(#date(2022,1,1))           // 44562
Number.From(#time(12,00,00))           // 0.5
Number.From(#datetime(2022,1,1,6,0,0)) // 44562.25
```

## Excel との違い

　以下のように、日付と日付をシリアル値に変換したテーブルを作成してみます。

```
let
    Source = 
        Table.FromRecords(
            {
                [No = 1, Val = #datetime(-1,1,1,0,0,00)],
                [No = 2, Val = #datetime(1,1,1,0,0,00)],
                [No = 3, Val = #datetime(99,12,31,0,0,0)],
                [No = 4, Val = #datetime(100,1,1,0,0,0)],
                [No = 5, Val = #datetime(100,1,1,1,0,0)],
                [No = 6, Val = #datetime(100,1,2,0,0,0)],
                [No = 7, Val = #datetime(100,1,2,1,0,0)],
                [No = 8, Val = #datetime(1899,12,30,0,0,0)],
                [No = 9, Val = #datetime(1900,1,1,0,0,0)],
                [No = 10, Val = #datetime(1900,2,29,0,0,0)],
                [No = 11, Val = #datetime(1900,3,1,0,0,0)],
                [No = 12, Val = #datetime(2022,10,22,13,05,00)],
                [No = 13, Val = #datetime(9999,12,31,23,59,59)],
                [No = 14, Val = #datetime(10000,1,1,0,0,0)]
            },
            type table [No = Int64.Type, Val = DateTime.Type]
        ),
    AddColumn = 
        Table.AddColumn(
            Source,
            "SerialNumber",
            each Number.From([Val]),
            Number.Type
        )
in
    AddColumn
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/74cffea6-69fe-3fe2-a382-ac5256504199.png)

　このテーブルをみると以下のことがわかります。

- マイナスの年号と10000年以上はエラーになります (No.1, No.14)
- 1年から999年までの日付はシリアル値に変換することができません (No.2, No.3)
- 1899年12月30日のシリアル値をゼロとして、それより前の日付はマイナスで表されます (No.4~8)
- 1900年2月29日はエラーになります(No.10)

　このテーブルを読み込んで、ビジュアルで表示させると、以下のようになります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/c12984da-11e2-62b7-bbde-adab1738b74a.png)

　ここで一か所注意する部分は、No.5の時刻が表示されないことです。**Power BIで有効な日付は100年1月1日からですが、時刻を含める場合は100年1月2日からとなります**。（参照「[Power BIデータモデルの型システム (1) 数値型、日付／時刻型](https://qiita.com/spumoni/items/c73e465b691b15694ba0#%E6%9C%80%E5%A4%A7%E6%97%A5%E4%BB%98%E3%81%A8%E6%9C%80%E5%B0%8F%E6%97%A5%E4%BB%98)」）

　このシリアル値をエクセルに入力して日付に変換してみます。（Microsoft Excel for Microsoft 365 MSO (バージョン2209)32ビットを使用）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/465ce3ea-9af6-5a8a-5856-3b6d2497a4f1.png)

　エクセルの日付の取り扱いは以下のとおりです。
- 日付にマイナスのシリアル値を持つことはできない
- 有効な日付は1900年1月1日から始まり、シリアル値は1で、Power BIと1日ずれる
- 1900年3月1日からはPower BIのシリアル値と同じ値になる

　このPower BIとエクセルのズレは、**エクセルが1900年2月29日が入力可能でPower BIは入力不可となっている違い**から生じています。

# 関連記事
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)