テーブル列を展開するときに Power Query を最適化する

  • [アーティクル]

Power BI ユーザーが迅速にデータを収集し、興味深く説得力のあるレポートを生成してインテリジェントなビジネス上の意思決定を行うことができるようにするためのシンプルで使いやすい方法です。ユーザーは、パフォーマンスの低いクエリを簡単に生成することもできます。 これは多くの場合、外部キーが SQL テーブルまたは SharePoint リストに関連するような形で、関連する 2 つのテーブルがある場合に発生します。 (念のために言っておきますが、この問題は SQL や SharePoint に固有のものではなく、多くのバックエンド データ抽出シナリオ、特にスキーマが流動的でカスタマイズ可能な場合に発生します。) また、共通のキーを共有する個別のテーブルにデータを保存することには、本質的に何の問題もありません。 —実際、これはデータベース設計と正規化の基本原則です。 しかし、それはリレーションシップを拡張するためのより良い方法を暗示しています。

SharePoint の顧客リストの例を次に示します。

プライマリ SharePoint 顧客リスト。

次に、参照先の場所の一覧を示します。

セカンダリ SharePoint 顧客リスト。

リストに初めて接続すると、その場所はレコードとして表示されます。

プライマリ ロケーション レコード。

この最上位レベルのデータは、任意の Web デバッガーで確認できる SharePoint API (メタデータ呼び出しを無視) に対する単一の HTTP 呼び出しによって収集されます。

Web デバッガーでの単一の HTTP 呼び出し。

レコードを展開すると、セカンダリ テーブルから結合されたフィールドが表示されます。

セカンダリ テーブルから結合されたフィールド。

あるテーブルから別のテーブルに関連する行を展開する場合、Power BI の既定の動作では、Table.ExpandTableColumn への呼び出しが生成されます。 これは、[生成された式] フィールドで確認できます。 残念ながら、このメソッドでは、最初のテーブルの行ごとに、2 番目のテーブルへの個別の呼び出しが生成されます。

2 番目のテーブルを個別に呼び出します。

これにより、プライマリ リストの各行に対する HTTP 呼び出しの数が 1 つ増えます。 これは、上記の 5 行や 6 行の例では大したことないと思われるかもしれませんが、SharePoint リストが数十万行になるような実稼働システムでは、大幅なエクスペリエンスの低下を招く可能性があります。

クエリがこのボトルネックになると、従来のテーブル結合を使用して行ごとの呼び出しを回避することが最も効果的な軽減策です。 これにより、2 番目のテーブルを取得するための呼び出しが 1 つだけになり、残りの展開は 2 つのテーブル間の共通キーを使用してメモリ内で行うことができます。 パフォーマンスの差は、場合によっては非常に大きくなることがあります。

最初に、元のテーブルを使用して展開する列を確認し、項目の ID があることを確認して、それを照合できるようにします。 通常、外部キーは、列の表示名に Id を付加したような名前になります。 この例では LocationId です。

外部キー名。

次に、セカンダリ テーブルを読み込み、外部キーである Id が含まれることを確認します。 [クエリ] パネルを右クリックし、新しいクエリを作成します。

Id 外部キーを使用してセカンダリ テーブルを読み込みます。

最後に、一致するそれぞれの列名を使用して、2 つのテーブルを結合します。 通常、このフィールドを検索するには、まず列を展開してから、プレビューで一致する列を探します。

プレビューでの列の一致。

この例では、プライマリ リストの LocationId がセカンダリ リストの Id と一致していることがわかります。 UI は、列名を一意にするために、この名前を Location.Id に変更します。 ここでは、この情報を使用してテーブルをマージします。

[クエリ] パネルを右クリックし、[新しいクエリ]>[結合]>[クエリーを新規として結合] を選択すると、これら 2 つのクエリを結合するためのわかりやすい UI が表示されます。

クエリを結合するには、[クエリーを新規として結合] を使用します。

ドロップダウンから各テーブルを選択すると、クエリのプレビューが表示されます。

マージされたクエリをプレビューします。

両方のテーブルを選択したら、テーブルを論理的に結合する列を選択します (この例では、プライマリ テーブルからの LocationId とセカンダリ テーブルの Id です)。 このダイアログ ボックスでは、その外部キーを使用して一致する行の数が表示されます。 この種類のデータには、既定の結合の種類 (左外部) を使用することが考えられます。

左外部結合の種類をマージします。

[OK] を選択すると、結合の結果である新しいクエリが表示されます。 レコードを展開しても、バックエンドへの追加の呼び出しは示されません。

左外部結合の結果。

このデータを更新すると、SharePoint への呼び出しは 2 回だけになります (プライマリ リストに対して 1 回、セカンダリ リストに対して 1 回)。 結合がメモリ内で実行され、SharePoint の呼び出し数が大幅に減少します。

この方法は、対応する外部キーを持つ PowerQuery 内の任意の 2 つのテーブルに対して使用できます。

Note

SharePoint のユーザー リストと分類はテーブルとしてもアクセスでき、ユーザーがこれらのリストにアクセスするための適切な特権を持っていれば、上記で説明したとおりに結合することができます。