パート 6-スキーマの TripPinTripPin Part 6 - Schema

このマルチパートチュートリアルでは、Power Query 用の新しいデータソース拡張機能の作成について説明します。This multi-part tutorial covers the creation of a new data source extension for Power Query. このチュートリアルは、 — 前のレッスンで作成したコネクタに対して各レッスンがビルドされ、コネクタに新しい機能が追加されていくことを目的としています。The tutorial is meant to be done sequentially—each lesson builds on the connector created in previous lessons, incrementally adding new capabilities to your connector.

このレッスンでは、次のことを行います。In this lesson, you will:

  • REST API に対して固定スキーマを定義するDefine a fixed schema for a REST API
  • 列のデータ型を動的に設定するDynamically set data types for columns
  • 列が存在しないことが原因で変換エラーを回避するためにテーブル構造を適用するEnforce a table structure to avoid transformation errors due to missing columns
  • 結果セットの列を非表示にするHide columns from the result set

標準 REST API での OData サービスの大きな利点の1つは、 $metadata 定義です。One of the big advantages of an OData service over a standard REST API is its $metadata definition. $Metadata ドキュメントでは、このサービスで検出されたデータ (すべてのエンティティ (テーブル) とフィールド (列) のスキーマを含む) について説明しています。The $metadata document describes the data found on this service, including the schema for all of its Entities (Tables) and Fields (Columns). 関数は、 OData.Feed このスキーマ定義を使用してデータ型の情報を自動的に設定します。これに — より、すべてのテキストフィールドと数値フィールドを取得するのではなく (からの場合と同様 Json.Document )、エンドユーザーは日付、整数、時刻などを取得し、全体的なユーザーエクスペリエンスを向上させることができます。The OData.Feed function uses this schema definition to automatically set data type information—so instead of getting all text and number fields (like you would from Json.Document), end users will get dates, whole numbers, times, and so on, providing a better overall user experience.

多くの REST Api には、プログラムによってスキーマを特定する方法がありません。Many REST APIs don't have a way to programmatically determine their schema. このような場合は、コネクタ内にスキーマ定義を含める必要があります。In these cases, you'll need to include schema definitions within your connector. このレッスンでは、テーブルごとに単純なハードコーディングされたスキーマを定義し、サービスから読み取ったデータにスキーマを適用します。In this lesson you'll define a simple, hardcoded schema for each of your tables, and enforce the schema on the data you read from the service.


ここで説明する方法は、多くの REST サービスに対して機能します。The approach described here should work for many REST services. 今後のレッスン は、構造化された列 (レコード、リスト、テーブル) にスキーマを再帰的に適用し、CSDL または JSON スキーマ ドキュメントからスキーマテーブルをプログラムによって生成できるサンプル実装を提供することで、この方法に基づいて構築されます。Future lessons will build upon this approach by recursively enforcing schemas on structured columns (record, list, table), and provide sample implementations that can programmatically generate a schema table from CSDL or JSON Schema documents.

全体として、コネクタによって返されるデータにスキーマを適用すると、次のような複数の利点があります。Overall, enforcing a schema on the data returned by your connector has multiple benefits, such as:

  • 正しいデータ型の設定Setting the correct data types
  • エンドユーザーに表示する必要のない列 (内部 Id や状態情報など) の削除Removing columns that don't need to be shown to end users (such as internal IDs or state information)
  • 応答から欠落している可能性のある列を追加することにより、データの各ページの構造が同じであることを確認する (REST Api でフィールドを null にする一般的な方法)Ensuring that each page of data has the same shape by adding any columns that might be missing from a response (a common way for REST APIs to indicate a field should be null)

テーブルスキーマを使用した既存のスキーマの表示Viewing the Existing Schema with Table.Schema

前のレッスンで作成したコネクタには、trippin サービス、、およびの3つのテーブルが表示され — Airlines Airports People ます。The connector created in the previous lesson displays three tables from the TripPin service—Airlines, Airports, and People. テーブルを表示するには、次のクエリを実行し Airlines ます。Run the following query to view the Airlines table:

    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]

結果には、次の4つの列が返されます。In the results you'll see four columns returned:

  • @odata.id
  • @odata.editLink
  • 放映 LinecodeAirlineCode
  • 名前Name


" @odata . *" 列は OData プロトコルに含まれており、コネクタのエンドユーザーに表示する必要のあるものではありません。The "@odata.*" columns are part of OData protocol, and not something you'd want or need to show to the end users of your connector. AirlineCodeName は、保持する2つの列です。AirlineCode and Name are the two columns you'll want to keep. (便利な テーブルスキーマ 関数を使用して) テーブルのスキーマを確認すると、テーブル内のすべての列のデータ型がであることがわかり Any.Type ます。If you look at the schema of the table (using the handy Table.Schema function), you can see that all of the columns in the table have a data type of Any.Type.

    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]


Table. スキーマ では、テーブル内の列に関する多くのメタデータが返されます。これには、名前、位置、型情報、および有効桁数、小数点以下桁数、MaxLength などの多くの高度なプロパティが含まれます。Table.Schema returns a lot of metadata about the columns in a table, including names, positions, type information, and many advanced properties, such as Precision, Scale, and MaxLength. 今後のレッスンでは、これらの高度なプロパティを設定するための設計パターンを提供していますが、ここでは ascribed type ( TypeName )、プリミティブ型 ( Kind )、および列の値が null () であるかどうかについてのみ考慮する必要があり IsNullable ます。Future lessons will provide design patterns for setting these advanced properties, but for now you need only concern yourself with the ascribed type (TypeName), primitive type (Kind), and whether the column value might be null (IsNullable).

単純なスキーマテーブルの定義Defining a Simple Schema Table

スキーマテーブルは、次の2つの列で構成されます。Your schema table will be composed of two columns:

Column 詳細Details
名前Name 列の名前。The name of the column. これは、サービスによって返される結果の名前と一致する必要があります。This must match the name in the results returned by the service.
TypeType 設定する M データ型。The M data type you're going to set. これには、プリミティブ型 (、、 text number datetime など)、または ascribed 型 (、など) を指定でき Int64.Type Currency.Type ます。This can be a primitive type (text, number, datetime, and so on), or an ascribed type (Int64.Type, Currency.Type, and so on).

テーブルのハードコーディングされたスキーマテーブルで Airlines は、列と列がに設定され、次の AirlineCode Name ようになり text ます。The hardcoded schema table for the Airlines table will set its AirlineCode and Name columns to text, and looks like this:

Airlines = #table({"Name", "Type"}, {
        {"AirlineCode", type text},
        {"Name", type text}

Airportsテーブルには、次の4つのフィールドを保持する必要があります (型の1つを含む record )。The Airports table has four fields you'll want to keep (including one of type record):

Airports = #table({"Name", "Type"}, {
        {"IcaoCode", type text},
        {"Name", type text},
        {"IataCode", type text},
        {"Location", type record}

最後に、 People テーブルには、リスト ( EmailsAddressInfo )、 null 許容 の列 ( Gender )、および ascribed 型 の列 () を含む7つのフィールドがあり Concurrency ます。Finally, the People table has seven fields, including lists (Emails, AddressInfo), a nullable column (Gender), and a column with an ascribed type (Concurrency).

People = #table({"Name", "Type"}, {
        {"UserName", type text},
        {"FirstName", type text},
        {"LastName", type text},
        {"Emails", type list},
        {"AddressInfo", type list},
        {"Gender", type nullable text},
        {"Concurrency", Int64.Type}

SchemaTransformTable ヘルパー関数The SchemaTransformTable Helper Function

SchemaTransformTable次に示すヘルパー関数は、データにスキーマを適用するために使用されます。The SchemaTransformTable helper function described below will be used to enforce schemas on your data. 使用できるパラメーターは次のとおりです。It takes the following parameters:

パラメーターParameter TypeType 説明Description
tabletable tabletable スキーマを適用するデータのテーブル。The table of data you'll want to enforce your schema on.
schemaschema tabletable 次の型の列情報を読み取るスキーマテーブル type table [Name = text, Type = type]The schema table to read column information from, with the following type: type table [Name = text, Type = type].
enforceSchemaenforceSchema numbernumber (省略可能)関数の動作を制御する列挙型。(optional) An enum that controls behavior of the function.
既定値 () を指定すると、不足している EnforceSchema.Strict = 1 列を追加し、余分な列を削除することによって提供されたスキーマテーブルと出力テーブルが一致することが保証されます。The default value (EnforceSchema.Strict = 1) ensures that the output table will match the schema table that was provided by adding any missing columns, and removing extra columns.
EnforceSchema.IgnoreExtraColumns = 2オプションを使用して、結果内の余分な列を保持できます。The EnforceSchema.IgnoreExtraColumns = 2 option can be used to preserve extra columns in the result.
EnforceSchema.IgnoreMissingColumns = 3を使用すると、欠落している列と余分な列の両方が無視されます。When EnforceSchema.IgnoreMissingColumns = 3 is used, both missing columns and extra columns will be ignored.

この関数のロジックは次のようになります。The logic for this function looks something like this:

  1. ソーステーブルに不足している列があるかどうかを確認します。Determine if there are any missing columns from the source table.
  2. 余分な列があるかどうかを確認します。Determine if there are any extra columns.
  3. 構造化列 (型、型、型、および型) を無視し list record table 、列をに設定 type any します。Ignore structured columns (of type list, record, and table), and columns set to type any.
  4. 各列の種類を設定するには、 TransformColumnTypes を使用します。Use Table.TransformColumnTypes to set each column type.
  5. スキーマテーブルに表示される順序に基づいて列の順序を変更します。Reorder columns based on the order they appear in the schema table.
  6. 値を置き換えて、テーブル自体の型を設定します。Set the type on the table itself using Value.ReplaceType.


テーブル型を設定する最後の手順では、クエリエディターで結果を表示するときに、Power Query UI が型情報を推論する必要がなくなります。The last step to set the table type will remove the need for the Power Query UI to infer type information when viewing the results in the query editor. これにより、 前のチュートリアルの最後に見た2つの要求の問題が解消されます。This removes the double request issue you saw at the end of the previous tutorial.

次のヘルパーコードをコピーして、拡張機能に貼り付けることができます。The following helper code can be copy and pasted into your extension:

EnforceSchema.Strict = 1;               // Add any missing columns, remove extra columns, set table type
EnforceSchema.IgnoreExtraColumns = 2;   // Add missing columns, do not remove extra columns
EnforceSchema.IgnoreMissingColumns = 3; // Do not add or remove columns

SchemaTransformTable = (table as table, schema as table, optional enforceSchema as number) as table =>
        // Default to EnforceSchema.Strict
        _enforceSchema = if (enforceSchema <> null) then enforceSchema else EnforceSchema.Strict,

        // Applies type transforms to a given table
        EnforceTypes = (table as table, schema as table) as table =>
                map = (t) => if Type.Is(t, type list) or Type.Is(t, type record) or t = type any then null else t,
                mapped = Table.TransformColumns(schema, {"Type", map}),
                omitted = Table.SelectRows(mapped, each [Type] <> null),
                existingColumns = Table.ColumnNames(table),
                removeMissing = Table.SelectRows(omitted, each List.Contains(existingColumns, [Name])),
                primativeTransforms = Table.ToRows(removeMissing),
                changedPrimatives = Table.TransformColumnTypes(table, primativeTransforms)

        // Returns the table type for a given schema
        SchemaToTableType = (schema as table) as type =>
                toList = List.Transform(schema[Type], (t) => [Type=t, Optional=false]),
                toRecord = Record.FromList(toList, schema[Name]),
                toType = Type.ForRecord(toRecord, false)
                type table (toType),

        // Determine if we have extra/missing columns.
        // The enforceSchema parameter determines what we do about them.
        schemaNames = schema[Name],
        foundNames = Table.ColumnNames(table),
        addNames = List.RemoveItems(schemaNames, foundNames),
        extraNames = List.RemoveItems(foundNames, schemaNames),
        tmp = Text.NewGuid(),
        added = Table.AddColumn(table, tmp, each []),
        expanded = Table.ExpandRecordColumn(added, tmp, addNames),
        result = if List.IsEmpty(addNames) then table else expanded,
        fullList =
            if (_enforceSchema = EnforceSchema.Strict) then
            else if (_enforceSchema = EnforceSchema.IgnoreMissingColumns) then
                schemaNames & extraNames,

        // Select the final list of columns.
        // These will be ordered according to the schema table.
        reordered = Table.SelectColumns(result, fullList, MissingField.Ignore),
        enforcedTypes = EnforceTypes(reordered, schema),
        withType = if (_enforceSchema = EnforceSchema.Strict) then Value.ReplaceType(enforcedTypes, SchemaToTableType(schema)) else enforcedTypes

TripPin コネクタを更新していますUpdating the TripPin Connector

ここで、新しいスキーマ強制コードを使用するように、コネクタに次の変更を行います。You'll now make the following changes to your connector to make use of the new schema enforcement code.

  1. SchemaTableすべてのスキーマ定義を保持するマスタースキーマテーブル () を定義します。Define a master schema table (SchemaTable) that holds all of your schema definitions.
  2. パラメーターを TripPin.Feed 受け入れるように、、およびを更新し GetPage GetAllPagesByNextLink schema ます。Update the TripPin.Feed, GetPage, and GetAllPagesByNextLink to accept a schema parameter.
  3. でスキーマを適用 GetPage します。Enforce your schema in GetPage.
  4. 新しい関数 () の呼び出しを使用して各テーブルをラップするようにナビゲーションテーブルコードを更新 GetEntity — します。これにより、将来、テーブル定義をより柔軟に操作できるようになります。Update your navigation table code to wrap each table with a call to a new function (GetEntity)—this will give you more flexibility to manipulate the table definitions in the future.

マスタースキーマテーブルMaster schema table

ここでは、スキーマ定義を1つのテーブルに統合し、 GetSchemaForEntity エンティティ名 (など) に基づいて定義を検索できるヘルパー関数 () を追加します GetSchemaForEntity("Airlines")You'll now consolidate your schema definitions into a single table, and add a helper function (GetSchemaForEntity) that lets you look up the definition based on an entity name (for example, GetSchemaForEntity("Airlines"))

SchemaTable = #table({"Entity", "SchemaTable"}, {
    {"Airlines", #table({"Name", "Type"}, {
        {"AirlineCode", type text},
        {"Name", type text}
    {"Airports", #table({"Name", "Type"}, {
        {"IcaoCode", type text},
        {"Name", type text},
        {"IataCode", type text},
        {"Location", type record}

    {"People", #table({"Name", "Type"}, {
        {"UserName", type text},
        {"FirstName", type text},
        {"LastName", type text},
        {"Emails", type list},
        {"AddressInfo", type list},
        {"Gender", type nullable text},
        {"Concurrency", Int64.Type}

GetSchemaForEntity = (entity as text) as table => try SchemaTable{[Entity=entity]}[SchemaTable] otherwise error "Couldn't find entity: '" & entity &"'";

データ関数へのスキーマサポートの追加Adding schema support to data functions

ここで schema 、、、およびの各関数に、省略可能なパラメーターを追加し TripPin.Feed GetPage GetAllPagesByNextLink ます。You'll now add an optional schema parameter to the TripPin.Feed, GetPage, and GetAllPagesByNextLink functions. これにより、スキーマを (必要に応じて) ページング関数に渡すことができます。この関数は、サービスから返される結果に適用されます。This will allow you to pass down the schema (when you want to) to the paging functions, where it will be applied to the results you get back from the service.

TripPin.Feed = (url as text, optional schema as table) as table => ...
GetPage = (url as text, optional schema as table) as table => ...
GetAllPagesByNextLink = (url as text, optional schema as table) as table => ...

また、これらの関数のすべての呼び出しを更新して、スキーマを確実に正しく渡すようにします。You'll also update all of the calls to these functions to make sure that you pass the schema through correctly.

スキーマの適用Enforcing the schema

実際のスキーマの適用は関数で実行され GetPage ます。The actual schema enforcement will be done in your GetPage function.

GetPage = (url as text, optional schema as table) as table =>
        response = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),        
        body = Json.Document(response),
        nextLink = GetNextLink(body),
        data = Table.FromRecords(body[value]),
        // enforce the schema
        withSchema = if (schema <> null) then SchemaTransformTable(data, schema) else data
        withSchema meta [NextLink = nextLink];

付箋この GetPage 実装では、 Table. fromrecords を使用して、JSON 応答内のレコードの一覧をテーブルに変換します。[Note] This GetPage implementation uses Table.FromRecords to convert the list of records in the JSON response to a table. Table. FromRecordsを使用する場合の大きな欠点は、リスト内のすべてのレコードが同じフィールドセットを持つということです。A major downside to using Table.FromRecords is that it assumes all records in the list have the same set of fields. これは TripPin サービスに対して機能します。 OData レコードは同じフィールドを含むように保証されますが、すべての REST Api に当てはまりない場合があるためです。This works for the TripPin service, since the OData records are guarenteed to contain the same fields, but this might not be the case for all REST APIs. より堅牢な実装では、 FromListテーブルの expandrecordcolumnを組み合わせて使用します。A more robust implementation would use a combination of Table.FromList and Table.ExpandRecordColumn. 後のチュートリアルでは、スキーマテーブルから列リストを取得するように実装を変更し、JSON から M への変換中に列が失われたり失われたりしないようにします。Later tutorials will change the implementation to get the column list from the schema table, ensuring that no columns are lost or missing during the JSON to M translation.

GetEntity 関数の追加Adding the GetEntity function

関数は、 GetEntity TripPin の呼び出しをラップします。The GetEntity function will wrap your call to TripPin.Feed. エンティティ名に基づいてスキーマ定義が検索され、完全な要求 URL が作成されます。It will look up a schema definition based on the entity name, and build the full request URL.

GetEntity = (url as text, entity as text) as table => 
        fullUrl = Uri.Combine(url, entity),
        schemaTable = GetSchemaForEntity(entity),
        result = TripPin.Feed(fullUrl, schemaTable)

TripPinNavTableGetEntity 、すべての呼び出しをインラインで実行するのではなく、関数を更新してを呼び出します。You'll then update your TripPinNavTable function to call GetEntity, rather than making all of the calls inline. このことの主な利点は、ナビゲーションテーブルロジックに触れることなく、エンティティの作成コードの変更を続けることができることです。The main advantage to this is that it will let you continue modifying your entity building code, without having to touch your nav table logic.

TripPinNavTable = (url as text) as table =>
        entitiesAsTable = Table.FromList(RootEntities, Splitter.SplitByNothing()),
        rename = Table.RenameColumns(entitiesAsTable, {{"Column1", "Name"}}),
        // Add Data as a calculated column
        withData = Table.AddColumn(rename, "Data", each GetEntity(url, [Name]), type table),
        // Add ItemKind and ItemName as fixed text values
        withItemKind = Table.AddColumn(withData, "ItemKind", each "Table", type text),
        withItemName = Table.AddColumn(withItemKind, "ItemName", each "Table", type text),
        // Indicate that the node should not be expandable
        withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true, type logical),
        // Generate the nav table
        navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")

まとめPutting it all Together

すべてのコード変更が行われたら、 Table.Schema 航空会社のテーブルを呼び出すテストクエリをコンパイルして再実行します。Once all of the code changes are made, compile and re-run the test query that calls Table.Schema for the Airlines table.

    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]

これで、航空会社のテーブルには、スキーマで定義した2つの列のみが含まれていることがわかります。You now see that your Airlines table only has the two columns you defined in its schema:


People テーブルに対して同じコードを実行する場合は、If you run the same code against the People table...

    source = TripPin.Contents(),
    data = source{[Name="People"]}[Data]

使用した ascribed の種類 ( Int64.Type ) も正しく設定されていることがわかります。You'll see that the ascribed type you used (Int64.Type) was also set correctly.


重要な点は、のこの実装では SchemaTransformTable 列と列の型が変更されないが、 list record 列と列はとし Emails AddressInfo て型指定さ list れていることです。An important thing to note is that this implementation of SchemaTransformTable doesn't modify the types of list and record columns, but the Emails and AddressInfo columns are still typed as list. これは、に Json.Document よって json 配列が m リストと json オブジェクトが m レコードに正しくマップされるためです。This is because Json.Document will correctly map JSON arrays to M lists, and JSON objects to M records. Power Query の一覧または [レコード] 列を展開した場合は、展開されているすべての列の型が any であることがわかります。If you were to expand the list or record column in Power Query, you'd see that all of the expanded columns will be of type any. 今後のチュートリアルでは、入れ子になった複合型の型情報を再帰的に設定する実装が改善されます。Future tutorials will improve the implementation to recursively set type information for nested complex types.


このチュートリアルでは、REST サービスから返される JSON データにスキーマを適用するための実装例を示しました。This tutorial provided a sample implementation for enforcing a schema on JSON data returned from a REST service. このサンプルでは、単純なハードコーディングされたスキーマテーブル形式を使用しますが、JSON スキーマファイルや、データソースによって公開されるメタデータサービス/エンドポイントなど、別のソースからスキーマテーブル定義を動的に構築することによって、この方法を拡張できます。While this sample uses a simple hardcoded schema table format, the approach could be expanded upon by dynamically building a schema table definition from another source, such as a JSON schema file, or metadata service/endpoint exposed by the data source.

列の型 (および値) を変更するだけでなく、コードもテーブル自体の正しい型情報を設定します。In addition to modifying column types (and values), your code is also setting the correct type information on the table itself. この型情報を設定すると、Power Query の内部で実行する場合のパフォーマンスが向上します。これは、ユーザーエクスペリエンスが常に正しい UI キューをエンドユーザーに表示するために型情報を推測しようとし、推定呼び出しによって基になるデータ Api への追加の呼び出しがトリガーされるためです。Setting this type information benefits performance when running inside of Power Query, as the user experience always attempts to infer type information to display the right UI queues to the end user, and the inference calls can end up triggering additional calls to the underlying data APIs.

前のレッスンの Trippin コネクタを使用して People テーブルを表示すると、すべての列に ' 型 any ' アイコン (リストを含む列も含む) があることがわかります。If you view the People table using the TripPin connector from the previous lesson, you'll see that all of the columns have a 'type any' icon (even the columns that contain lists):


このレッスンの TripPin コネクタで同じクエリを実行すると、型情報が正しく表示されることがわかります。Running the same query with the TripPin connector from this lesson, you'll now see that the type information is displayed correctly.


次のステップNext steps

パート7の TripPin M 型の高度なスキーマTripPin Part 7 - Advanced Schema with M Types