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

Last updated at 2024-01-20Posted at 2022-10-23

（この記事は2022年10月22日の「PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #19」で使用したものです）

　プリミティブ型(Primitive types)はプリミティブ値 (binary、date、datetime、datetimezone、duration、list、logical、null、number、record、text、time、type) を分類するものであり、また、これにはさまざまな抽象型 (function、table、any、none) が含まれます。

1. text （テキスト型）

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

二重引用符

"He said ""run"" and so I did." //  -> He said "run" and so I did.

改行

"Hello
World"

エスケープシーケンスを使ってエンコード

"Hello#(lf)World"     // UNIX形式改行
"Hello#(cr,lf)World"  // Windows形式改行
"Hello#(tab)World"  // タブ挿入

16進数を使用してUnicodeを挿入

"Hello #(4e16,754c)" // Hello 世界

Escape Escapeで#を表示

"Part Code #(#)(2501)"  // Part Code #(2501)

比較

"a" = "a" // true
"a" > "b" // false
Compare.Equals(Comparer.OrdinalIgnoreCase, "A", "a")  // true

結合

"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

Text.Format

比較関数

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

date

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

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 を指しますが、全く同じではありません。

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の引数にはマイナスを使用することもできます。

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

変換

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への変換で、タイムゾーンのオフセットが含まれない場合は、ローカルのタイムゾーンが設定されます。

文字列

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

組み合わせ

#date(2018,4,30) & #time(15,30,10) = #datetime(2018,4,30,15,30,10)

Excelの日時シリアル値 OADate（OLEオートメーション日付）

　Excelでは、日時をシリアル値で扱います。Power QueryでOADateのシリアル値を使うことができます。

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

　時間については、有効桁数が足りない場合、期待する時間とずれてしまう可能性があります。

OADate

Time.From(0.209039351851)   // 05:01:00.9999999

　Number.Fromを使用して OADate値に変換することができます。

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