Power Query を使用する場合のベスト プラクティス

これらのPower Queryベスト プラクティスは、クエリのパフォーマンスの向上、クエリ フォールディングの活用、正しいデータ型の選択、変換の整理、およびパラメーターとカスタム関数を使用したロジックの再利用に役立ちます。 これらは、Power Query Desktop と Power Query Online の両方のエクスペリエンスに適用されます。

適切なコネクタを選択する

Power Queryには、多くのデータ コネクタが用意されています。 これらのコネクタは、TXT、CSV、Excel ファイルなどのデータ ソースから、Microsoft SQL Serverなどのデータベース、Microsoft Dynamics 365や Salesforce などの一般的なサービスとしてのソフトウェア (SaaS) 製品まで多岐に及びます。 [ データの取得 ] ウィンドウで専用コネクタを使用できない場合は、ODBC や OLE DB などの汎用コネクタを使用します。

データ ソースが使用可能な場合は、専用のコネクタを選択します。 たとえば、SQL Server コネクタは、SQL Server データベースに接続するときに、汎用 ODBC コネクタよりも優れたデータ取得エクスペリエンスを提供します。 SQL Server コネクタは、クエリ フォールディングなどのパフォーマンス機能もサポートしています。 詳細については、Power Queryでのクエリ評価とクエリ フォールディングの概要に関するページを参照してください。

各データ コネクタは、「 データの取得」で説明されている標準的なエクスペリエンスに従います。 この標準化されたエクスペリエンスには、 Data Preview というステージがあります。 この段階では、コネクタが許可する場合に、データ ソースから取得したいデータを選択するための使いやすいウィンドウと、そのデータの簡易プレビューが提供されます。 [ナビゲーター] ウィンドウを使用して、データ ソースから複数のデータ セットを選択することもできます。

データを早期にフィルター処理してパフォーマンスを向上させる

データをできるだけ早くフィルター処理して、後の変換で処理Power Query行の数を減らします。 クエリ フォールディングをサポートするコネクタの場合、Power Queryは、Power Queryでのクエリ評価とクエリ フォールディングの概要に関するページで説明されているように、フィルターをデータ ソースにプッシュバックできます。 無関係なデータをフィルターで除外すると、データ プレビューに表示されるデータも制限されます。

自動フィルター メニューを使用して、列に見つかった値の個別の一覧を表示し、保持または除外する値を選択します。検索バーを使用して、列内の値を見つけるのに役立ちます。

型固有のフィルターを利用して、日付、datetime、または日付タイムゾーン列に対して過去にといったフィルターを適用することもできます。

これらの種類固有のフィルターは、以前の x 秒、分、時間、日、週、月、四半期、または年数のデータを常に取得する動的フィルターを作成するのに役立ちます。

フィルター行のダイアログのスクリーンショットで、前の日付に基づくフィルターが表示されている。

コストの高い操作を最後に実行してパフォーマンスを向上させる

Power Query エディターでプレビューのパフォーマンスを向上させるには、最後にコストのかかる操作を実行します。 特定の操作では、何らかの結果を返すためにデータ ソース全体を読み取る必要があるため、プレビュー表示に時間がかかります。 たとえば、並べ替えを実行すると、最初のいくつかの並べ替えられた行がソース データの末尾にある可能性があります。 結果を返すには、並べ替え操作でまずすべての行を読み取る必要があります。

その他の操作 (フィルターなど) では、結果を返す前にすべてのデータを読み取る必要はありません。 代わりに、"ストリーミング" と呼ばれる方法でデータを操作します。 データが "ストリーム" され、結果が途中で返されます。 Power Query エディターでは、このような操作は、プレビューを設定するのに十分な量のソース データを読み取るだけで済みます。

可能であれば、このようなストリーミング操作を最初に実行し、よりコストの高い操作を最後に実行します。 この順序で操作を実行すると、新しいステップをクエリに追加するたびにプレビューがレンダリングされるのを待つ時間を最小限に抑えることができます。

クエリの開発中にデータ サブセットを使用する

Power Query エディターでの新しいステップの追加が遅い場合は、[最初の行を保持] を使用して、クエリの開発中に処理されるデータを制限します。 必要なすべての手順を追加した後、[最初の 行を保持 ] ステップを削除して、完了したクエリで完全なデータ セットが処理されるようにします。

適切なデータ型を使用する

各列に適切なデータ型を設定Power Query、型固有の変換とフィルターを使用できるようにします。 たとえば、日付列を選択すると、[列の追加] メニューの [日付と時刻] 列グループのオプションを使用できます。 列にデータ型が設定されていない場合、これらのオプションは淡色表示されます。

型固有のフィルターは特定のデータ型に固有であるため、同様の状況が発生します。 列に正しいデータ型が定義されていない場合、これらの型固有のフィルターは使用できません。

列の正しいデータ型を常に操作することが重要です。 データベースなどの構造化データ ソースを操作すると、データベース内のテーブル スキーマからデータ型情報が取得されます。 ただし、TXT や CSV ファイルなどの非構造化データ ソースの場合は、そのデータ ソースからの列に適切なデータ型を設定することが重要です。 既定では、Power Query では、非構造化データ ソースに対して自動データ型検出が提供されます。 この機能の詳細と、 それがデータ型でどのように役立つかを確認できます。

データをプロファイリングして探索する

データを準備して変換手順を追加する前に、Power Query データ プロファイル ツールを有効にして、データに関する情報を検出します。

Power Queryには、次の 3 つのデータ プロファイル ツールが用意されています。

データの準備に役立つこれらの機能を操作することもできます。

作業を文書化する

手順、クエリ、グループにわかりやすい名前と説明を付けることで、Power Query ソリューションを文書化します。 これらの詳細により、各変換の目的を理解し、維持しやすくなります。

Power Query では、適用されたステップ ウィンドウにステップ名が自動的に作成されますが、ステップの名前を変更したり、いずれかのステップに説明を追加したりすることもできます。

大規模なクエリをモジュールに分割する

大規模なPower Query クエリをより小さな参照先クエリに分割して、変換フェーズの理解と保守を容易にします。 1 つのクエリに必要なすべての変換と計算を含めることができますが、1 つのクエリが次のクエリを参照する場合、多くのステップを持つクエリを管理する方が簡単です。

たとえば、次のクエリには 9 つのステップがあり、 Merge と Prices テーブル のステップが含まれています。

このクエリは、[ Merge with Prices]\(価格とのマージ\) テーブル ステップで 2 つに分割できます。 こうすることで、マージの前に販売クエリに適用された手順を理解しやすくなります。 この操作を行うには、[ 価格とのマージ] テーブル ステップを右クリックし、[ 前へ抽出 ] オプションを選択します。

その後、新しいクエリに名前を付けるダイアログが表示されます。 この手順では、クエリを効果的に 2 つのクエリに分割します。 1 つのクエリには、マージ前のすべての手順があります。 もう 1 つのクエリでは、新しいクエリを参照する初めのステップと、「Merge with Prices テーブル」ステップ以下にある元のクエリの残りの手順が含まれています。

必要に応じてクエリ参照を使用することもできます。 ただし、多くの手順で一見すると難しくないレベルでクエリを維持することをお勧めします。

クエリをグループに整理する

[クエリ] ウィンドウでグループを使用して、作業を整理します。

グループの唯一の目的は、クエリのフォルダーとして機能することで作業を整理し続けることです。 必要に応じて、グループ内にグループを作成できます。 グループ間でのクエリの移動は、ドラッグ アンド ドロップと同じくらい簡単です。

自分とケースにとって意味のあるわかりやすい名前をグループに付けてみてください。

将来を見据えたクエリ

将来の更新が引き続き成功するように、ソース データの予想される変更を処理するようにクエリを設計します。 Power Queryでは、データ ソース内の行、列、または値が変更されたときにクエリの回復性を高める変換が提供されます。

構造、レイアウト、列名、データ型、およびその他の関連コンポーネントの観点から、クエリの実行内容と考慮すべき内容など、クエリのスコープを定義します。

次の変換は、クエリが変更に対する回復性を維持するのに役立ちます。

パラメーターを使用する

Power Query パラメーターを使用して、変換、データ ソース関数、およびカスタム関数で再利用できる値を格納および管理します。 パラメーターを使用すると、クエリを使用する各クエリを編集する代わりに、1 つの場所の値を変更できるため、クエリの更新が容易になります。 次の 2 つの一般的なシナリオがあります。

• ステップ引数: ユーザー インターフェイスから駆動される複数の変換の引数としてパラメーターを使用します。

  

• カスタム関数引数: クエリから新しい関数を作成し、カスタム関数の引数としてパラメーターを参照します。

  

パラメーターを作成して使用する主な利点は次のとおりです。

• [ パラメーターの管理 ] ウィンドウを使用して、すべてのパラメーターの一元化されたビュー。

  

• 複数のステップまたはクエリでのパラメーターの再利用性。

• カスタム関数の作成を簡単かつ簡単にします。

データ コネクタの引数の一部でパラメーターを使用することもできます。 たとえば、SQL Server データベースに接続するときに、サーバー名のパラメーターを作成できます。 その後、SQL Server データベース ダイアログ内でそのパラメーターを使用できます。

サーバーの場所を変更する場合は、サーバー名のパラメーターを更新するだけで、クエリが更新されます。

再利用可能な関数を作成する

異なるクエリまたは値に同じ変換セットを適用する必要がある場合は、Power Queryカスタム関数を作成します。 Power Queryカスタム関数は、入力値のセットを 1 つの出力値にマップし、ネイティブ Power Query M 数式言語関数と演算子から作成されます。

たとえば、同じ変換セットを必要とする複数のクエリまたは値があるとします。 後で任意のクエリまたは値に対して呼び出すカスタム関数を作成できます。 このカスタム関数は、時間を節約し、一元的な場所で変換のセットを管理するのに役立ちます。これはいつでも変更できます。

Power Query カスタム関数は、既存のクエリとパラメーターから作成できます。 たとえば、複数のコードをテキスト文字列として持ち、それらの値をデコードする関数を作成するクエリがあるとします。

最初に、例として機能する値を持つパラメーターを指定します。

そのパラメーターから、必要な変換を適用する新しいクエリを作成します。 この場合、PTY-CM1090-LAX コードを複数のコンポーネントに分割します。

• Origin = PTY
• 目的地 = LAX
• 航空会社 = CM
• FlightID = 1090

その後、クエリを右クリックして [関数の作成] を選択すると、そのクエリを 関数に変換できます。 最後に、任意のクエリまたは値にカスタム関数を呼び出すことができます。

さらにいくつかの変換が行われると、目的の出力に達し、カスタム関数からこのような変換のロジックを適用したことがわかります。