0
Help us understand the problem. What are the problem?

posted at

updated at

[Power BI] Power Queryの『型』を探る (1) number

(この記事は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) が含まれます。
image.png

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)
0除算の表記
#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進数型で計算が行わるため、誤差は発生しません。

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で扱う型には、ファセットと呼ばれる情報を付与することができます。ファセットはあくまで付加情報なので、動作に何ら影響を与えることはなく、使う必要はほとんどないでしょう。もし、開発者が異なった型をもつ外部システムとデータとのやり取りをする場面で、注意深く作業を行う場合は役に立つかもしれません。

facets
let
    TypeWithFacets =                // テキスト型に対してファセットを3つ設定
        Type.ReplaceFacets(
            type text, 
            [
                MaxLength = 25,
                IsVariableLength = true,
                NativeTypeName = "NVARCHAR"
            ]
        ),

    Source = 
        Table.FromRecords(
            {[Txt = "Hello World!"]},
            type table[
                Txt = TypeWithFacets  // ファセットを設定した型を適用
            ]
        )
in
    Source

 このファセットは以下のような設定になります。
image.png

 また、Table.Schemaで表示させると以下のようになります。

名称未設定.png

 このファセットの内容は、Power Queryエディッター、Power BI Desktop上で何ら影響がありません。たとえ、MaxLengthが5となっていても"Hello World!"の12文字は切り詰められることはありません。

型の変換

 Power Queryでは、数字にはnumber型、文字列にはtext型のように、最初にプリミティブ型が適用され、列の型は any が適用されます。
image.png
 この列に対して型を設定すると、それはファセットの1種であるtype claimとして登録されます。あくまでファセットと同様にデータに対しての操作は発生しません。

TypeClaim
let
    Source = 
        Table.FromRecords(
            {
                [Value = 1.23],
                [Value = "Hello World!"],
                [Value = true]
            },
            type table [Value = Number.Type]
        )
in
    Source

image.png

 列の型はNumber.Typeになっているのですが、文字列や論理型の値も表示されています。
 ところが、Table.TransformColumnTypesで型変換を行うと、それぞれの項目に応じた変換作業が行われます。
image.png

ChangeType
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を設定したあと、その型への変換作業を行います。以下のような変換関数が呼び出され、変換できないものについてはエラーになります。
image.png

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

image.png

 しかし、整数型(Int64.Type)に変換された列の値を操作すると、列の型はふたたび any になってしまいます。

ChangeValue
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}  // 値に1加えると列の型はany型に変わる
            }
        )
in
    ChangedValue

image.png

4. 型の検出

 データベースなどの構造化データソースに接続するとき、テーブルスキーマを読み取って自動的に表示されます。
 Excel、CSV、テキストファイルなどの非構造化ソースでは、オプションの設定により、テーブル内の値を調べてデータ型が自動的に検出されます。
 また、「変換」タブの「データ型の検出」コマンドを使用して、テーブル内の列のデータ型を自動的に検出することもできます。
image.png

データ型の検出
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
    ]}
)

image.png
小数点が含まれない場合は整数型(Int64.Type)となり、それ以外は全てnumber型となります。

オプション

 Power BI Desktopのオプションでデータの読み込み時にソースの型の検出についての設定ができます。(Power Query Onlineにも設定があります)

image.png
image.png

5. クエリエラー

 列の型と値の型が異なっていても、Power Queryエディター上ではエラーになりませんでした。

TypeClaim2
let
    Source = 
        Table.FromRecords(
            {
                [Value = 1],
                [Value = 2.3456],         // 整数ではない
                [Value = "Hello World!"], // テキスト型
                [Value = true]            // 論理型
            },
            type table [Value = Int64.Type] // 列は整数型
        )
in
    Source

image.png
 しかし、Power Queryエディターを閉じてPower BI Desktopに戻ると、以下のようなエラーになってしまいます。
image.png
 整数型でない値は変換作業は行われず、全てエラーとなって読み込むことができませんでした。
image.png

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

image.png
 このテーブルをTable.Schemaで表示すると、以下のようになります。
image.png
 いずれも、TypeNameはNumber.Type、Kindはnumberとして設定されます。

6. 変換作業は必要か

 型の変換作業を行うと、列の値が型に合致するよう変換できます。また、変換できない値はエラーとなり、問題があることを警告してくれます。列の型と異なる値がテーブルに存在する場合、変換操作で修正するか、きちんと取り除いてやり、意図せぬデータの欠落は避けることが必要です。

 データ型の変換作業を行うタイミングは、「 できるだけ早い時期に 」行うのがよいでしょう。

 値が完全に型に一致していることが保証されているのであれば、変換作業にコストを支払う必要はありませんが、そのコストは敢えて避ける必要があるほど大きいものとはなりません。

 型ファセットは主に外部(データソース、ホスト環境、ツール)と相互作用するときに関係することを忘れないでください。ファセットは言語層やマッシュアップエンジン層の動作に影響を与えません。ファセットを適用し、「.Type」で終わる名前を定義しても、新しいタイプやサブタイプは生成されるわけではありません。

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
0
Help us understand the problem. What are the problem?