テクテク日記

テクテク＝テクノロジー&一歩ずつ（テクテク）

Power Queryで全クエリのメタデータ情報を簡単集計

Power Query

前回は、#sectionsキーワードを使用してPower Queryにクエリを追加する際に、自動的に集計を行う方法について説明しました。今回は、この手法を活用して各テーブルのメタデータ情報を集計する方法についてご紹介します。

なお、この方法はデータモデリングのシーンにおいて、各テーブルの列の選択やデータ型の変更（特にローカルファイルなどを使用する場合）などの作業に活用することができます。

シナリオ

Power BIのデータモデリングは、スタースキーマに基づくDimテーブルとFactテーブルをリレーションシップで関連付ける作業です。このプロセスにおいて、次の重要なポイントがあります。

  1. スリムなモデルの追求
  2. データモデルとDAXはペアで考える
  3. スタースキーマ + シンプルなDAXメジャー

これらの中でも、1は不必要な列や行を取り除き、データ量を最適化することが重要であることを強調しています。

そのため、ソースデータの分析（データ内容の詳細検討）において、テーブルと列の情報を制限することが一つの鍵となります。この中で、列は属性とも呼ばれる要素であり、Power BIではTable.Schemaを使用することでこれを簡単に特定できます。

サンプルデータの抽出

今回は、以下のODataフィードをデータソースとして活用します。ODataフィードは、RESTfulなWebサービスを介してデータにアクセスするためのプロトコルであり、異なるデータソースやアプリケーション間でデータを共有・操作するための標準的な手段を提供します。クライアントアプリケーションは、ODataフィードを使用してデータを取得し、必要な操作を行うことができ、異なるシステム間でのデータ連携が簡単になります。

データソース（ODataフィード）
https://services.odata.org/V4/Northwind/Northwind.svc/

なお、Odataフィードについては下記ブログが非常に分かりやすいです。

以下データを取得するやり方となります。

  1. Power BI DesktopからOData.Feedコネクタで接続
  2. パラメーターを設定し、ODataフィードのURLを入力
  3. 設定を確認し、テーブルを全て選択
  4. 成功すると、全てのクエリを取得できていることを確認できる
  5. クエリペインにて、右クリック > 新しいクエリ > 空のクエリ > ★All_Schemaという名前のクエリを作成し、「読み込みを有効にする」のチェックを外す
  6. 数式バーに#sections[Section1]を入力
  7. テーブル化
  8. ★とSourceという名前を含む行を除外
  9. Value列に対してTable.Schemaを使ったSchema列を追加
  10. Name列をTableにリネーム
  11. Schema列の中身（各テーブルのメタデータ情報）を新しい列に展開

ソースコードと結果は以下の通り。

let
    Source = #sections[Section1],
    ToTable = Record.ToTable(Source),
    // ★とSource（パラメーター）以外のテーブル（ODataフィード）を全て抽出
    Filtered = Table.SelectRows(
        ToTable, each not Text.Contains([Name], "★") and not Text.Contains([Name], "Source")
    ),
    /* Table.AddColumnでSchemaという列を追加し、Table.Schema関数で列Value（各テーブルのデータ）の各列の属性を取得。
    最後にName（テーブル名）と追加したSchemaという列だけを選択（注：[[列名A], [列名B]]はTable.SelectColumns(前ステップ, {"列名A", "列名B"})と同じ） */
    Schema = Table.AddColumn(Filtered, "Schema", each Table.Schema([Value]))[[Name], [Schema]],
    //列NameをTableにリネーム
    Renamed = Table.RenameColumns(Schema, {{"Name", "Table"}}),
    //各テーブルのスキーマ（Name = 列名、Position = 左から数えて何番目に当たるかという数字（ゼロスタート）、TypeName = 詳細なデータ型、Kind = データ型の種類）を展開
    Expanded = Table.ExpandTableColumn(
        Renamed, "Schema", 
        {"Name", "Position", "TypeName", "Kind"}, 
        {"Name", "Position", "TypeName", "Kind"}
    )
in
    Expanded

ODataフィードはデータベースや他のデータソースから抽出されるデータであるため、TypeName（列の型の名前）やKind（列の型の種類）は定義済となっています。従って、抽出されたデータに対して改めてデータ型を指定する必要はありません。

一方、ODataフィードではなく、ExcelやCSV等のローカルファイルからデータを抽出した場合、自分でデータ型を指定する必要があるため、そのやり方については次回以降に解説します。

メタデータ情報を別のクエリとしてコピー

抽出されたテーブルを少し眺めてみます。Kindで絞ってみると、binaryやrecord、tableといった列タイプが存在することが分かりました。これらのデータはそのままでは使用することはできないため、新たな行や列へと展開する必要があります。

不必要な場合は、フィルターを使用して外すことができますが、今回は新たに★header_masterというクエリを作成します。★All_Schemaクエリは5列204行というデータ量（5列 × 204行 = 1,020セル）ですので、「データの入力」の制限*1を下回っています。そのため、以下のように新しいクエリを作成できます。

このクエリを作成した理由は前回記事にて言及した”落とし穴”が関係しており、★All_Schemaをヘルパーテーブル*2としてそのまま使用した場合、Power BI Desktopはデータを読み込むことができません。更に、モデリングに必要な列をヘルパーテーブルで定義した列名から選択できないため、どちらにせよ、★header_masterクエリを作る必要があります。

カスタム関数

今回のやり方をカスタム関数にすることで、除外キーワード（2つまで）を入力して一発でテーブルスキーマ情報を取得できるようにしました。以下のソースコードでfnGetSchemaという名前のカスタム関数を作ることができます。

let fnGetSchema = (word_excluded as text, word_excluded_02 as nullable text) =>
    let
        Source = #sections[Section1],
        ToTable = Record.ToTable(Source),
        // Value列にあるエラー削除
        RemoveErrors = Table.RemoveRowsWithErrors(ToTable, {"Value"}),
        // Value列のタイプを抽出し、Table(=1)のデータだけを残す
        Filtered = Table.SelectRows(
            RemoveErrors, each not Text.Contains([Name], "★") and not Text.Contains([Name], "Source") and 
            Number.From(Type.Is(Value.Type([Value]), type table)) = 1
        ),
        /* Table.AddColumnでSchemaという列を追加し、Table.Schema関数で列Value（各テーブルのデータ）の各列の属性を取得。
        最後にName（テーブル名）と追加したSchemaという列だけを選択（注：[[列名A], [列名B]]はTable.SelectColumns(前ステップ, {"列名A", "列名B"})と同じ） */
        Schema = Table.AddColumn(Filtered, "Schema", each Table.Schema([Value]))[[Name], [Schema]],
        //列NameをTableにリネーム
        Renamed = Table.RenameColumns(Schema, {{"Name", "Table"}}),
        //各テーブルのスキーマ（Name = 列名、Position = 左から数えて何番目に当たるかという数字（ゼロスタート）、TypeName = 詳細なデータ型、Kind = データ型の種類）を展開
        Expanded = Table.ExpandTableColumn(
            Renamed, "Schema", 
            {"Name", "Position", "TypeName", "Kind"}, 
            {"Name", "Position", "TypeName", "Kind"}
        )
    in
        Expanded
in fnGetSchema

最後に

データモデリングにおけるメタデータ情報はテーブル定義書*3に相当します。Power BIにおけるモデリングのベストプラクティスは、前述の1～3の方法として述べられていますが、今回の手法は必要な列やデータ型などの情報を取得するために便利なアプローチの1つとなります。

*1:行と列の値が合計3,000セルを超えた場合、入力することができません

*2:データモデリングや計算のサポートを目的として作成される補助的なテーブルで、例えばSalesテーブルに必要な列をヘルパーテーブルで定義した列で選択したり、データ型をアサインしたりすることが可能

*3:テーブル定義書は、データベース内のテーブルに関する重要な情報をまとめた文書。テーブル名、カラム（列）、主キー、外部キー、制約（NOT NULL制約等）、インデックス、説明等の情報を含むドキュメンテーション