（この記事は2022年9月17日の「PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #18」で使用したものです）
Power Queryにはプリミティブ型(primitive-type)という基本の型があります。これは、プリミティブ値 (binary、date、datetime、datetimezone、duration、list、logical、null、number、record、text、time、type) を分類するものであり、また、これにはさまざまな抽象型 (function、table、any、none) が含まれます。
1. numberの基本
値の記述方法は以下のようになります。
3.14 // Fractional number
.5 // Fractional number
-1.5 // Fractional number
1.0e3 // Fractional number with exponent
.2e25 // Fractional number with exponent
123 // Whole number
1e3 // Whole number with exponent
0xff // Whole number in hex (255)
#infinity // produced by an expression like 1/0
-#infinity // produced by an expression like -1/0
#nan // produced by an expression like 0/0
0で除算を行った場合、Power Queryは #infinity を返します。また0を0で除算したり #infinity を #infinityで除算した場合は #nan (NaN 非数 Not a Number）を返します。これはエラーではありません。Power Queryの特別な値です。
2. numberは倍精度浮動小数点
Power Queryで扱う数値型は、常に倍精度浮動小数点が用いられます。
したがって、他のコンピューター計算と同様に小数点の計算で誤差が発生します。
0.1 + 0.2 // 0.30000000000000004
10000000000000000 + 1 // 10000000000000000
0.10000000000000001 = 0.1 // true - 倍精度での比較
10000000000000000 = 10000000000000001 // true - 倍精度での比較
以下のように10進数型(decimal)の精度で計算するように明示すると、内部で10進数型で計算が行わるため、誤差は発生しません。
Value.Add(10000000000000000, 1, Precision.Decimal) // 10000000000000001
Value.Compare(0.10000000000000001 , 0.1, Precision.Decimal) // false
Value.Compare(10000000000000000 , 10000000000000001, Precision.Decimal) // false
計算にかかるシステムのコストよりも正確さが最優先されるような場合は、Value関数を使用する必要があります。
3. type claim (型要求)
Powre Queryでテーブルを作成するとき、列の型として使えるnubmer型のサブタイプに以下のものがあります。
| アイコン | サブタイプ名 | TypeName | プリミティブ型名 |
|---|---|---|---|
 |
10進数 | Number Type | number |
 |
固定小数点 | Currency.Type | number |
 |
整数 | Int64.Type | number |
 |
パーセンテージ | Percentage Type | number |
また、それ以外にも整数型は Int8.Type, Int16.Type, Int32.Type 、符号なし8ビット整数の Byte.Type があります。また、10進数型は、Single.Type, Double.Type や、固定小数点数型は Decimal.Type があります。
これらは、マッシュアップエンジンの観点からはnumber型であり、.Typeで表される型はプリセットされたtype claimです。しかし、外部に渡されたデータがこの型情報を使用して効率的な計算や保存ができるようになる可能性があります。
ファセット (facets)
Power Queryで扱う型には、ファセットと呼ばれる情報を付与することができます。ファセットはあくまで付加情報なので、動作に何ら影響を与えることはなく、使う必要はほとんどないでしょう。もし、開発者が異なった型をもつ外部システムとデータとのやり取りをする場面で、注意深く作業を行う場合は役に立つかもしれません。
let
TypeWithFacets = // テキスト型に対してファセットを３つ設定
Type.ReplaceFacets(
type text,
[
MaxLength = 25,
IsVariableLength = true,
NativeTypeName = "NVARCHAR"
]
),
Source =
Table.FromRecords(
{[Txt = "Hello World!"]},
type table[
Txt = TypeWithFacets // ファセットを設定した型を適用
]
)
in
Source
このファセットは以下のような設定になります。
また、Table.Schemaで表示させると以下のようになります。
このファセットの内容は、Power Queryエディッター、Power BI Desktop上で何ら影響がありません。たとえ、MaxLengthが5となっていても"Hello World!"の12文字は切り詰められることはありません。
型の変換
Power Queryでは、数字にはnumber型、文字列にはtext型のように、最初にプリミティブ型が適用され、列の型は any が適用されます。
この列に対して型を設定すると、それはファセットの1種であるtype claimとして登録されます。あくまでファセットと同様にデータに対しての操作は発生しません。
let
Source =
Table.FromRecords(
{
[Value = 1.23],
[Value = "Hello World!"],
[Value = true]
},
type table [Value = Number.Type]
)
in
Source
列の型はNumber.Typeになっているのですが、文字列や論理型の値も表示されています。
ところが、Table.TransformColumnTypesで型変換を行うと、それぞれの項目に応じた変換作業が行われます。
let
Source =
Table.FromRecords(
{
[Value = 1.23],
[Value = "Hello World!"],
[Value = true]
},
type table [Value = Number.Type]
),
ChangedType =
Table.TransformColumnTypes(
Source,
{
{"Value", Int64.Type}
}
)
in
ChangedType
Table.TransformColumnTypesは、type claimを設定したあと、その型への変換作業を行います。以下のような変換関数が呼び出され、変換できないものについてはエラーになります。
| 値 | base type | 変換関数 | 結果 |
|---|---|---|---|
| 1.23 | number | Int64.From(1.23) | 1 |
| Hello | text | Int64.From("Hello World!") | DataFormat.Error: Number に変換できませんでした。 |
| true | logical | Int64.From(true) | 1 |
しかし、整数型(Int64.Type)に変換された列の値を操作すると、列の型はふたたび any になってしまいます。
let
Source =
Table.FromRecords(
{
[Value = 1.23]
},
type table [Value = Number.Type]
),
ChangedType =
Table.TransformColumnTypes(
Source,
{
{"Value", Int64.Type} // 整数型に変換
}
),
ChangedValue =
Table.TransformColumns(
ChangedType,
{
{"Value", each _ + 1} // 値に１加えると列の型はany型に変わる
}
)
in
ChangedValue
4. 型の検出
データベースなどの構造化データソースに接続するとき、テーブルスキーマを読み取って自動的に表示されます。
Excel、CSV、テキストファイルなどの非構造化ソースでは、オプションの設定（下記参照）により、テーブル内の上位1000行の値を調べてデータ型が自動的に検出されます。
また、「変換」タブの「データ型の検出」コマンドを使用して、テーブル内の列のデータ型を自動的に検出することもできます。
Source = Table.FromRecords(
{[
Col1 = 3.14, // type number
Col2 = .5, // type number
Col3 = -1.5, // type number
Col4 = 1.0e3, // Int64.Type
Col5 = .2e25, // type number
Col6 = 123, // Int64.Type
Col7 = 1e3, // Int64.Type
Col8 = 0xff, // Int64.Type
Col9 = #infinity, // type number
Col10 = #nan // type number
]}
)
小数点が含まれない場合は整数型（Int64.Type）となり、それ以外は全てnumber型となります。
オプション
Power BI Desktopのオプションでデータの読み込み時にソースの型の検出についての設定ができます。（Power Query Onlineにも設定があります）
5. クエリエラーと丸め処理
列の型と値の型が異なっていても、Power Queryエディター上ではエラーになりませんでした。
let
Source =
Table.FromRecords(
{
[No = 1, Value = 1],
[No = 2, Value = 5.6789], // 整数ではない
[No = 3, Value = "Hello World!"], // テキスト型
[No = 4, Value = true] // 論理型
},
type table [No = Int64.Type, Value = Int64.Type] // 列は整数型
)
in
Source
Power Queryエディターを閉じてPower BI Desktopに戻ると、以下のようなエラーになってしまいます。
整数型でない値は変換作業は行われず、全て空白(BLANK)となってしまいます。
一方で、読み込む前に型の変換を行った場合は以下のようになります。
let
Source =
Table.FromRecords(
{
[No = 1, Value = 1],
[No = 2, Value = 5.6789], // 整数ではない
[No = 3, Value = "Hello World!"], // テキスト型
[No = 4, Value = true] // 論理型
},
type table [No = Int64.Type, Value = Int64.Type] // 列は整数型
),
// 型の変換を行う
ChangedType =
Table.TransformColumnTypes(
Source,
{
{"Value", Int64.Type}
}
)
in
ChangedType
変換できなかった文字列はエラーとなりますが、10進数型は丸め処理が行われ、論理型はTrueが１、Falseが0になります。
銀行型丸め
型の変換で10進型が整数に丸め処理されていますが、このとき行われる丸め処理の種類は 「銀行型丸め(RoundingMode.ToEven)」 です。
銀行型丸めでは、最も近い整数に丸められ、中間の値は最も近い偶数に丸められます。つまり、整数に変換する場合、以下のようになります。
| 元の数 | 丸め後 | |
|---|---|---|
| 1.4 | 1 | |
| 1.5 | 2 | 偶数２に丸め |
| 1.6 | 2 | |
| 2.4 | 2 | |
| 2.5 | 2 | 偶数2に丸め |
| 2.6 | 3 |
このように、小数部が5の場合、整数部が偶数になる方に丸めが行われます。
Power Queryの Number.Round では、この銀行丸めが標準となっていますので、通常の四捨五入を行う場合はオプションで RoundingMode.AwayFromZero を指定します。
Round = Table.TransformColumns(
Source,
{
{
"Value",
each Number.Round(_, 0, RoundingMode.AwayFromZero)
}
}
)
5. type number と Number.Type
Table.FromRecordの型として設定するとき,type number という書き方と Number.Type という書き方の両方使うことができます。
let
Source =
Table.FromRecords(
{
[
Col1 = 1.23,
Col2 = 1.23
]
},
type table [
Col1 = (type number), // type numberはカッコで括る必要があります
Col2 = Number.Type
]
)
in
Source
このテーブルをTable.Schemaで表示すると、以下のようになります。
いずれも、TypeNameはNumber.Type、Kindはnumberとして設定されます。
6. 変換作業は必要か
型の変換作業を行うと、列の値が型に合致するよう変換できます。また、変換できない値はエラーとなり、問題があることを警告してくれます。列の型と異なる値がテーブルに存在する場合、変換操作で修正するか、きちんと取り除いてやり、意図せぬデータの欠落は避けることが必要です。
データ型の変換作業を行うタイミングは、「 できるだけ早い時期に 」行うのがよいでしょう。
値が完全に型に一致していることが保証されているのであれば、変換作業にコストを支払う必要はありませんが、そのコストは敢えて避ける必要があるほど大きいものとはなりません。
型ファセットは主に外部（データソース、ホスト環境、ツール）と相互作用するときに関係することを忘れないでください。ファセットは言語層やマッシュアップエンジン層の動作に影響を与えません。ファセットを適用し、「.Type」で終わる名前を定義しても、新しいタイプやサブタイプは生成されるわけではありません。