Power Query へそのゴマ 第１４章 型システム

　この章では、Power Queryにおけるデータ型、ファセット、型指定（Type Claim）に関連する操作や概念を詳しく解説します。

14.1. 型の概要

Power Queryでは、すべてのデータは型を持ちます。型は、データの性質を定義し、変換や計算における処理方法を決定します。

14.1.1 データ型指定の重要性

M言語は、動的型付けのクエリ言語です。変数の型やデータの型を事前に宣言する必要はありません。しかし、型を明示的に指定しないことは、データ処理においてリスクを生みます。

明確さと一貫性

例えば、請求書番号の列が、1000, 1001, 1002 と並んでおり、型を明確にしていない場合、数値型として扱ってしまうかもしれません。しかし、220件目以降は TA-2001, TA-2002 となっている可能性があり、期待通りの処理が行われないことになってしまいます。

データ型が明確に定義されていると、データの性質を把握でき、全てのデータを自信をもって扱うことができます。

データ検証とエラーの防止

カスタム関数を作成する際、データ型が特に重要になります。関数を定義するときは、各パラメータの期待されるデータ型と返される結果の型を定義しておくことをお勧めします。

( myText1 as text, myText2 as text ) as text => myText1 & myText2

この関数は2つのテキストの値を入力として受けとり、それらを結合したテキストを返します。データ型を明示的に設定することで、Power Queryエンジンに対して渡される値が宣言されたデータ型に準拠してい必要があることを伝え、入力を検証します。

関数が異なる型の入力値を受け取った場合は、エラーをスローしてユーザーに問題があることを伝えます。

パフォーマンスの最適化

その他、データ型を定義することで、Power Queryはデータの保存および取得方法を最適化し、パフォーマンスを最適化 します。型を指定していない場合、Power Queryは型を推論しようとし、追加の計算リソースが必要になります。

互換性確保

テーブルをマージする際にも、マージに使用する列はデータ型が一致していなければいけません。また、他のシステムと連携をする場合、互換性を確保するためにデータ型を合わせておくことが重要です。異なった型へ渡された値は、問題が発生することがあります。

14.1.2. 代表的なプリミティブ型(Primitive Types)

1. 非抽象型

• number: 数値（整数、浮動小数点）
• text: テキストデータ
• logical: 真偽値（true または false）
• date, time, datetime, datetimezone: 日付や時刻データ
• duration: 時間間隔データ
• binary: バイナリデータ
• null: null
• list: 順序付けられたデータのコレクション（カスタム型）
• type 型値

2. 抽象型

• record: 名前と値のペアのコレクション（カスタム型）
• table: 行と列を持つデータ構造（カスタム型）
• any: あらゆる型のデータを受け入れる抽象型
• anynonnull: nullを除くすべての値
• function: 関数値を分類（カスタム型）
• none: 何の値も分類しません

14.1.3 Nullable プリミティブ型

• 標準のプリミティブ型にnullを加える

MyFunction = (x as number, y as number) => x + y

上記関数は、MyFunction(11, null)はエラーになります。

MyFunction = (x as number, y as nullable number) => x + y

この場合は、MyFunction(11, null)はエラーになりません。
ただし、MyFunction(11)はエラーになります。第2引数を省略するには、optionalを付けます。

MyFunction = (x as number, optional y as number) => x + y

この場合は、MyFunction(11)もMyFunction(11, null)もエラーになりません。

Int64.Type（整数）、Currency.Type（固定小数点数）、およびNumber.Type（小数点数）などの型は含まれていません。これは、これらがType Claimsであり、ファセットカテゴリに属しています。

14.1.4 型の検出

データは通常、テーブルの形式で提供され、テーブルの列にはデータ型があります。デフォルトの設定を使用すると、Power Query はインポートしたテーブルのデータ型を自動的に認識します。ただし、Power Query がデータセット内の値のデータ型をどのように検出するかは、使用するデータソースと Power Query の設定に依存します。

データソースからデータ型を取得する

構造化されたデータソース（SQL、Oracle、Azure Data Lake、ODataなど）からデータをインポートする際、Power Queryはこれらのデータソースのテーブルスキーマを参照します。テーブルスキーマにはテーブルの構造が定義されており、各列のデータ型に関する情報が含まれています。Power Queryは、これらのデータ型の情報に従って型を設定します。

自動的に型を検出する

構造化されていないデータソース（Excelファイル、CSV、テキストファイルなど）には定義されたスキーマが存在しないため、インポートする際に最初の200行を調べて、各列に最も適したデータ型を決定します。

ただし、Power Queryの設定で、この機能を変更するオプションがあります。

Power BI Desktopの「ファイル」から「オプションと設定」→「オプション」を選択します。

グローバル設定の「データの読み込み」で「型の選択」に3つのラジオボタンがあります。

• 常に：各ファイルの各列の最初の200行を調べ、Power Queryのパターン認識アルゴリズムによって、各列の最も可能性の高いデータ型を導き出します。
• 各ファイルの設定に応じて：自動型検出の使用は、現在のファイルの「データ読み込み」設定に従います。
• 検出しない：これは、Power Queryが列の型を認識するために「変更された型」のステップを自動的に挿入するのを防ぎます。

また、「現在のファイル」にも「型の検出」のチェックボックスがあります。

ここで型の検出を自動で行わないようにしておくと、データソースの読み込み時に自動で「昇格されたヘッダー数」と「変更された型」のステップが作成されなくなります。

自動検出機能は便利ですが、その分析は最初の200行に基づいています。従って、各列のデータ型を確認し手作業で調整することは、正確性を確保するためのベストプラクティスです。

値の型を変換する

1. 型抽出関数：Date.From, Text.From, Number.From などの関数を使い、特定の型を識別します。
2. 型変換関数：Date.ToText, Binary.ToTextは、日付やバイナリデータをテキスト型に変換します。Date.FromText, Binary.FromTextは、テキストを`日付やバイナリデータに変換します。

列型の変換

Table.TransformColumnTypesを使用して、列の型を変換します。この関数は、2つの操作を実行します。

1. 指定された列へデータ型の割り当て
2. 既存の値を新しいデータ型に変換

列のデータ型を変更すると、ヘッダーのアイコンが変更されます。ただし、値の変換は常に成功するわけではありません。変換できない値はエラーになります。

データ型の変換には、以下の4つの結果をもたらす可能性があります。

• 成功
• データ損失とともに成功: データ型の変換が行われますが、一定レベルのデータ損失を伴います
• データ獲得とともに成功: データ型の変換が行われますが、一定レベルのデータ獲得を伴い、獲得されたデータが正しい場合も、間違っている場合もあります。
• 失敗

ロケール/カルチャー

Power Queryは、世界中の様々な形式が使用できるようになっています。デフォルトでコンピュータの言語と地域設定が反映しているため、違う言語と地域設定の環境では異なる動作をする可能性があります。

例えば、Date.From関数には、以下の様にカルチャーがオプションに設定できます。

Date.From(
    value as any, 
    optional culture as nullable text
) as nullable date

カルチャーには、en-USやja-JPなどを用います。

関数のドキュメントでcultureパラメータがある場合、それはカルチャコードを使用して入力や出力に影響を与えることができることを示しています。関数にcultureパラメータが利用可能な場合は、それを使用することをお勧めします。これにより、ユーザーのマシンの言語と地域の設定に関係なく出力が予測可能になります。

14.2 ファセット

Power Queryでは、型に追加情報を付け加えるための「ファセット」という注釈を使用できます。このファセットはあくまで情報を提供するだけのもので、型や値そのものの動作には直接影響を与えません。主に外部システムやツールとやり取りする際に役立ちます。

例えば、あるWebサービスが返すテーブルには、文字列の列や数値の列が含まれることがあります。Power Queryではそれらの列がそれぞれ「text型」「number型」であることを認識しますが、ファセットを使うことで「文字列は最大25文字まで」「数値は整数のみ」などの追加情報を付与できます。この情報は、マッシュアップエンジンの処理には影響しませんが、開発者がデータを扱う際に理解を深める助けになります。

逆に、Power BIやExcelなど外部環境へデータを出力する際、列の型情報にファセットを付与することで、外部システムがその型情報を基に効率的にデータを処理できます。例えば、Power Queryでは「number型」として認識される列に「整数型」のファセットを付与すると、Power BIではその列が「整数型（whole number）」として認識されます。

ファセットを設定

let
  SomeType = type text,
  Facets = 
    [ 
      MaxLength = 25,   // 最大25文字まで
      IsVariableLength = true, // 可変長をサポート
      NativeTypeName = "NVARCHAR"  // 可変長のUnicode文字列
    ],
   TypeWithFacets = Type.ReplaceFacets(SomeType, Facets)
in
  TypeWithFacets 

ファセットを参照

Type.Facets(TypeWithFacets) 

ファセットには大きく分けて2種類があります。
1つ目は「単純ファセット（Simple Fasets）」で、これはデータコネクタが生成するデータに関する追加情報を提供します。例えば、文字列の最大長（MaxLength）や可変長であるかどうか（IsVariableLength）、データベースで使用される型名（NativeTypeName）などが含まれます。この情報はType.ReplaceFacetsを使用して設定でき、Type.Facetsを使えばその情報を読み取ることができます。

2つ目は「タイプ・クレーム（Type Claims）」で、外部システムの型分類をPower Query内で表現するための標準化された情報です。例えば、Int8.TypeやCurrency.Typeといった事前定義されたタイプ・クレームがあり、これらをValue.ReplaceTypeを使って値に適用できます。

ファセット自体はデータ処理には影響しませんが、外部システムではこれを基に動作が変わることがあります。例えば、Power BIで列に「64ビット整数型」のファセットが付与されている場合、その列に小数が含まれるとエラーになります。このような場合、Table.TransformColumnTypesを使用して型変換を行いながらファセットを設定するのが推奨されます。

ファセットは主にテーブル型に関連して使用され、外部システムとのデータ交換を円滑にするために役立ちます。一方で、数値の演算結果や関数呼び出しで自動的に引き継がれることはありません。ただし、テーブル操作やリスト操作では一部の関数がファセット情報を保持します。

14.3. タイプ・クレーム（Type Claim）

14.3.1 タイプ・クレームとは

Power Queryでは、数値をすべて「number型」、文字列をすべて「text型」として扱うなど、単純な型システムを使用しています。しかし、外部システムでは、数値型を「整数」「小数」「浮動小数点」などに細分化し、さらにそれぞれを8ビット、16ビットなどに分類することもあります。

このような外部システムの型分類に対応するため、Power Queryでは「タイプ・クレーム（Type Claims）」という仕組みを使用します。タイプ・クレームは、システムに依存しない方法で外部システムの型をPower Query内で表現するための標準化された情報です。たとえば、Int8.TypeやCurrency.Typeといった型クレームがあり、これらを使ってデータがどのような型を持つかを外部システムに伝えることができます。

タイプ・クレームはすべての型にデフォルトで設定されており、たとえば「text型」のデフォルトクレームはText.Type、「date型」はDate.Typeです。ただし、タイプ・クレームの真価は、事前定義されたクレームを使用することで、より具体的な型情報を外部システムと共有できる点にあります。

これらの型クレームは、Power Queryの標準ライブラリに定義されています。たとえば、Int8.Typeは8ビット整数を示す型クレームを持つ型を返す名前であり、Currency.Typeは通貨型を示す型クレームを持つ型を返します。

型クレームを確認する方法としては、標準ライブラリのテーブル機能を使用するのが一般的です。また、型クレームを抽出する関数を作成することで、任意の型に関連付けられた型クレームを簡単に確認することもできます。

let
    TypeClaimFacet = 
        (input as type) as text => 
            Table.SingleRow(
                Table.Schema(　#table(　type table [Col1 = input], {}　)　)
            )[TypeName]
in
    TypeClaimFacet(type number) // "Number.Type" -- the default type claim for type number
//  TypeClaimFacet(Int8.Type) // "Int8.Type"
//  TypeClaimFacet(Int16.Type) // "Int16.Type"
//  TypeClaimFacet(Currency.Type) // "Currency.Type"

この関数を使用することで、たとえばtype numberのデフォルト型クレームは"Number.Type"であることが確認できます。また、特定の型クレーム（Int8.TypeやCurrency.Typeなど）を持つ型の情報も取得できます。

型クレームは、主に外部システムとのやり取りやテーブルのデータ型を表現する際に活用されます。そのため、型クレームを正確に指定することで、外部システムでのデータ処理が効率化される場合があります。

Power Queryでは、すべての値に自動的に型が割り当てられます（例: 数値にはnumber型、文字列にはtext型）。この割り当てられた型を直接変更することはできませんが、Value.ReplaceTypeを使うことで、新しい型情報を持つ値を生成できます。この方法により、元の値はそのままに、ファセット（追加情報）や非デフォルトの型クレームを付与することが可能です。

ただし、この操作で値の基本型（base type）は変わりません。たとえば、数値に新しい型を付与しても、基本型は依然としてnumber型のままです。また、付与する型は、値の基本型と構造的に互換性がある必要があります。たとえば、数値にはnumber型やその派生型しか割り当てられず、数値をtext型に変えることはできません。

カスタム型を使用する場合、型の割り当ては少し複雑になることがありますが、それでも「構造的に互換性がある型」というルールは維持されます。この点を理解しておくと、型の管理がよりスムーズに行えます。

14.3.2 利用可能なタイプ・クレーム

• Type Claimsとそれに対応する規定データ型 ("The Definitive Guide to Power Query")
  

14.3.3 タイプ・クレームを使用した値の変換

基底型またはタイプクレームを使用すると、次の数値型でわずかに異なる動作が発生する可能性があります。

let
    Source = Table.FromRows(
        {
            List.Repeat({"4.1234567890123"}, 4)
        },
        type table [
            #"type number" = text,
            Int64.Type = text,
            Currency.Type = text,
            Percentage.Type = text
        ]
    ),

    // 型変換
    #"Changed Type" = 
        Table.TransformColumnTypes(
            Source,
            {
                {"type number", type number}, 
                {"Int64.Type", Int64.Type}, 
                {"Currency.Type", Currency.Type}, 
                {"Percentage.Type", Percentage.Type}
            }
        )
in
    #"Changed Type"

それぞれの型の条件に合わせて精度が失われています。Int64.Typeに変換した後に、type numberに再度変換しなおしても制度は失われたままです。

値の型を変換するためにタイプクレームを使用すると、その値の精度が失われ、取り戻せなくなる可能性があります。

14.3.4 タイプ・クレームを調べる

let
    myType = Character.Type,
    mySchema = 
        Table.Schema( 
            #table(type table [Col1 = myType], {} ) 
        ),
    typeInfo = mySchema[[TypeName], [Kind]]
in
    typeInfo

このコードは、タイプ名とその種類を表示するレコードを返します。上記の例では、Character.Typeが基底型textに属していることを示しています。

14.4. 型の付与（Ascribing Types）

14.4.1 型の付与とは

Power Queryでは、すべての値に自動的に型が割り当てられます（例: 数値にはnumber型、文字列にはtext型）。この割り当てられた型を直接変更することはできません。

型の付与とは、値自体の変更せずにその型をラベル付けします。型の付与では、限定的な適合チェックが行われます。このチェックは、宣言した型が値のプリミティブ型と互換性があることを確認します。ただし、この操作で値の基本型（base type）は変わりません。たとえば、数値に新しい型を付与しても、基本型は依然としてnumber型のままです。

例えば、数値のみを含む列にテキスト型とラベル付けしても、実際の値は数値として保存されます。そのため、これらの値にテキスト専用の関数を適用できません。

let
    Source = 
        Table.FromRows(
            {
                {"a", 1},
                {"b", 2},
                {"c", 3}
            },
            type table [Text = text, Number = text]
        ),
        
    Transform =
        Table.TransformColumns(
            Source,
            {
                {
                    "Number",
                    each Text.Length(_)
                }
            }
        )
in
    Transform

上記テーブルのNumber列にText.Lengthを適用すると、エラーになります。

列のデータ型を変換するとき、システムは値にラベル付けし、互換性をチェックし、新しいデータ型に変換します。もし値が変換できないときは、エラーを表示します。しかし、型の付与ではエラーは発生しません。

型の付与を行うメリットは、以下の3つです。

1. パフォーマンス：全てのデータをスキャンする必要がないため、パフォーマンスを向上させる可能性があります。
2. リスク管理：列に付与された型と互換性のない値は、Power BIなどのアプリケーションにデータをロードする際、エラーを発生させる可能性があります。付与された型と実際のデータに矛盾があった場合のリスクを管理できます。
3. 外部アプリケーションとの互換性：外部アプリケーションとのデータ交換に際し、互換性を保つようにします。

14.4.2 型の付与をサポートする関数

レコードの作成時

Record.FromLisなどの関数は、カスタムレコード型をサポートしています。

Record.FromList(
    { 1, "Alice", 20 },
    type [ ID = number, Name = text, Age = number ]
)

テーブル作成時

テーブルの作成を行う関数には、#table, #Table.FromRecords, Table.FromList, Table.FromColumns, Table.FromRows, Table.FromValue` などがあります。

#table(
  type table[ BookID = Int64.Type, Title = text ],
  { { 1, "Animal Farm" }, { 2, "Brave New World" } }
)

テーブルの修正時

テーブルの作成に加えて、テーブルの修正時にも型を付与するための関数があります。

Table.TransformColumns(
    Source,
    {
        {
            "Title", 
            Text.Upper,
            type text
        }
    }
)

14.4.3 任意の値に型を付与する

Value.RepalceTypeは、特定の値のデータ型を置き換える専用関数です。

Value.ReplaceType( 5, type number)

プリミティブ型だけでなく、より複雑なカスタム型を付与することもできます。

Value.ReplaceType( 
    {"a", "b", "c"},
    type { text }
)

また、データ型と一致しない型を付与することもできます。

Value.ReplaceType( 
    {"a", "b", "c"},
    type { number }
)

このような場合でも、エラーは発生しません。

14.4.4 型の付与によるエラー

型の付与によるエラーは、以下の状況で発生します。

• 付与された型の基底型が、割り当てられた値と互換性がない場合。

  Value.ReplaceType( "myString", type number )
  

  

• 基底型は互換性があるが、タイプクレーム自体が値と一致していない場合。

  Value.ReplaceType( 5.33353, Int64.Type )
  

  Power Queryではエラーが発生していませんが、Power BIに読み込むときに以下のようなエラーとなります。
  
  「エラーの表示」をクリックしても、残念ながら以下の様に表示されるだけです。
  

• 付与された型が、型と互換性のない値を含む構造化された値に適用されている場合。

  Value.ReplaceType( [A = 1, B = 2 ], Int64.Type )
  

  
  これは、以下の様に修正することでエラーは発生しなくなります。

  Value.ReplaceType( 
      [A = 1, B = 2 ], 
      type [A = Int64.Type, B = Int64.Type] 
  )
  

---