Power Query 言語フローのベスト プラクティス

このチュートリアルでは、Power Query 言語フローとそのベスト プラクティスのいくつかについて学びます。また、可能な限り最良の結果を得るためにデータを簡単に変換する方法も学びます。データ変換によりレポートが最適化され、魅力的なレポートになります。

目次

• Power Query 言語フローを使用せずにデータ型を設定する
  • 「変換」タブを使用してデータ型を設定する
  • 列ヘッダーを使用してデータ型を設定する
  • 列アイコンを使用してデータ型を設定する
• Power Query 言語フローを使用してデータ型を設定する
  • 不要な列を削除する
  • 適切なデータ型を列に割り当て、行を制限する
  • Power Query 言語フローの冗長なステップを統合する
• クエリを整理して Power Query 言語フローを改善する
• Power Query 言語フローの概要
• 結論

Power Query 言語フローを使用せずにデータ型を設定する

常に実行する必要がある最低限の変換は、列の名前変更とデータ型の設定です。

データ型は、Mの値を分類するために使用されます。列名の横にあるアイコンは、フィールドに割り当てられたデータ型を示します。このサンプル テーブルでは、列名の横に ABC123 アイコンが表示されます。そのアイコンは Any データ型を表し、その列にデータ型が割り当てられていないことを示します。 

ブック以外では、自動タイプ検出が有効になっています。これにより、クエリ内に変更されたタイプのステップが自動的に生成されます。これが有効になっている場合でも、Power Query がすべてのテーブルの各列のデータ型を正しく推測していることを常に確認してください。

この設定を有効または無効にするには、次の手順に従います。

「グローバル」オプションで、使用可能な3 つのタイプ検出オプションのいずれかを選択できます。

[現在のファイル] オプションで、グローバル設定に応じてタイプ検出のオプションを切り替えることができます。

「変換」タブを使用してデータ型を設定する

ユーザー インターフェイスには、列のデータ型を設定する複数の方法が用意されています。「変換」タブの「任意の列」セクションには、 「データ型の検出」ボタンがあります。

すべての列を選択してそのボタンをクリックすると、すべての列のデータ型が自動的に設定されます。ただし、スキャンされるのは上位 200 行だけであるため、Power Query のデータ型が正しいかどうかを検証する必要があります。

テーブルから列を選択すると、[任意の列] セクションにそのデータ型が表示されます。

ドロップダウン ボタンをクリックして [テキスト] を選択すると、選択した列のアイコンがABC123からABCのみに変わります。

[適用されたステップ] ペインに [変更されたタイプ] ステップが表示されます。

[ホーム] タブには、データ型を設定するオプションもあります。

列ヘッダーを使用してデータ型を設定する

もう 1 つの方法は、ヘッダーを右クリックして[タイプの変更]を選択することです。そこから、列に適切なデータ型を選択できます。

列アイコンを使用してデータ型を設定する

データ型を設定する最も一般的な方法は、列名の横にあるアイコンをクリックすることです。利用可能なすべてのデータ型が表示されます。

サンプル テーブルでは、OrderDate列のデータ型をDateに設定します。

選択した列のデータ型に応じて、Power Query の [変換] タブ、 [列の追加] タブ、および [スマート フィルター] セクションで、その特定のデータ型に適用される一連の変換とオプションが提供されます。

したがって、日付列を選択して [変換] タブに移動すると、[日付] ボタンの下にオプションが表示されます。

ただし、テキスト列を選択した場合、「日付」ボタンの下のオプションは使用できなくなります。

スマート フィルターの場合、テキスト列にはテキスト フィルターがあり、日付列には日付フィルターがあります。

次の列では、列名をCustomer Keyに、データ型をWhole Numberに変更します。

次に、Channel、Currency Code、および Warehouse Code 列のデータ型をテキストに変更します。

次に、配信地域インデックスの名前を配信地域キーに変更し、そのデータ型をWhole Numberに変更します。次に、 「製品説明インデックス」列と「注文数量」列に対しても同じことを行います。

最後に、[単価]、[行合計]、および [単価] 列のデータ型を [固定 10 進数]に設定します。

Power Query 言語フローを使用してデータ型を設定する

列の変更により、[適用されたステップ] ペインに多くのステップが作成されました。問題は、同じ種類の変換を複数回実行するため、サンプルテーブル クエリが非効率になることです。これは避けるべきことです。

クエリを効率的にするには、別のステップを作成する前に、その特定の変換をすべての列に適用して単一のステップを作成してみてください。

サンプル テーブル クエリを複製し、名前をBest Practiceに変更します。[適用されたステップ] ペインで、前のクエリで実行された最初の変換を右クリックし、 [最後まで削除]を選択します。次に、 「ステップ削除」ダイアログでステップの削除を確認します。

さらに、ベスト プラクティスをいくつか紹介します。最初に、データ ソースの場所を含むパラメーターを作成します。これにより、ファイル名が変更された場合の問題を修正しやすくなります。

パラメータを作成するには、「ホーム」タブの「パラメータの管理」をクリックし、 「新規パラメータ」を選択します。

もう 1 つの方法は、[クエリ] ペインを右クリックして[新しいパラメータ]を選択することです。

その後、「パラメータの管理」ダイアログボックスが表示されます。パラメータにFileLocation という名前を付け、タイプを Text に設定します。[推奨される値] を[値のリスト]に設定すると、文字列を貼り付けて、切り替え可能な複数の場所を変更または追加できるようになります。

次に、ファイル エクスプローラーに移動してファイルを選択します。パスをコピーしてパラメータを貼り付けます。完了したら、「OK」を押します。

ベスト プラクティス クエリに戻り、[適用されたステップ] ペインでソース ステップをクリックします。次に、数式バーのハードコーディングされたファイル パスをFileLocationで変更します。

不要な列を削除する

不要な列をすべて削除してスペースを節約し、パフォーマンスを向上させます。テーブルと列の追加は削除するよりもはるかに簡単であるため、必要なデータのみを持ち込んでください。

これを行う最も簡単な方法は、[ホーム] タブの[列の選択]ステップを使用することです。このボタンをクリックすると、ダイアログ ボックスが表示され、保持する列を選択できます。実行する分析に最適な特定の目的を持ってテーブルを設計および形成する必要があります。

そこから、テーブル内で不要な列の選択を解除します。このサンプル テーブルでは、OrderNumber 列のカーディナリティが高くなります。ファイル サイズと全体的なパフォーマンスに影響を与えるため、その列の選択を解除することをお勧めします。

サンプル クエリの分析に位置データが必要ない場合は、 [配達地域インデックス] の選択を解除することも最適です。最後に、テーブルにはすでに単価と数量が設定されているため、[行合計] 列は必要ありません。

列の選択を解除した後、[OK]を押します。列の選択を変更する場合は、[適用されたステップ] ペインのステップ名の横にある歯車アイコンをクリアします。

適切なデータ型を列に割り当て、行を制限する

次に、すべての列にデータ型を割り当てます。すべての列を選択し、「変換」タブの「データ型の検出」をクリックします。

データ型の検出は、上位 200 行のスキャンに基づいて列のデータ型を自動的に検出します。したがって、Power Query が正しいデータ型を設定していることを確認してください。

行数を制限することをお勧めします。会計年度が 7 月 1 日に始まる場合は、6 月のデータを省略したり、パラメーターを設定したりできます。

ただし、 LuckyTemplates サービスでパラメーター値を変更できるようにするには、落とし穴があります。レポートを公開した後、パラメーター値はテキスト タイプまたは 10 進数タイプのいずれかである必要があります。

これに対処するには、OrderDate 列のドロップダウン ボタンをクリックしてデータにフィルターを作成します。次に、「日付フィルター」をクリックし、「後」を選択します。

ダイアログ ボックスで、最初のパラメータを「以降」または「等しい」に変更し、日付を入力します。この例では、入力された日付は 2014 年 7 月 1 日です。

OKを押すと、フィルターがテーブルに適用されます。次に、パラメーターを作成し、DatesFromという名前を付けます。[タイプ] を[テキスト]に設定し、[現在の値] パラメーターに日付を入力します。

次に、ベスト プラクティス クエリに戻り、組み込みの#date をDate.From(DatesFrom)に置き換えます。

Date.From関数を使用せずにチェック記号をクリックすると、エラーが返されます。これは、フィールドが Date 型であるのに対し、DatesFrom は Text 型であるためです。Date.From関数は、テキストを日付に変換します。

データ モデルで非表示にならないすべての列の名前を変更します。名前は、簡潔で、自己記述的で、ユーザーフレンドリーである必要があります。レポートを使用するユーザー向けにデータ モデルを設計していることに留意してください。

Power Query 言語フローの冗長なステップを統合する

次に行うことは、冗長な手順(名前の変更、削除、列のデータ型の変更など) を統合することです。さらに、列の並べ替えなどの手順は、冗長性を探すときに注意する必要があります。

データ モデルにロードしたテーブルはレポートには表示されません。これにより、列の順序は無関係になります。

もう 1 つのベスト プラクティスは、[適用されたステップ] ペインでステップの名前を変更することです。ステップの名前は自己記述的であり、Mコード内の変数として使用されます。

スペースまたは特殊文字を含む名前は引用符で囲まれた表記法を使用して記述されます。これは、名前が一連の二重引用符で囲まれ、名前の前にハッシュ記号またはシャープ記号が付いていることを意味しており、これにより Mコードが読みにくくなります。スペースを省略したり、スペースの間にアンダースコアを挿入したりできます。

[詳細エディター] ウィンドウにコメントを入力してドキュメントの詳細を追加することも、Power Query のベスト プラクティスです。[ステップ プロパティの説明]でこれを行うこともできます。これらは、[適用されたステップ] ペインで感嘆符の付いたステップの上にマウスを移動すると、ツールチップの注釈として表示されます。

初期の開発で特定の選択を行った理由を知ることは、しばらくしてからファイルを再度参照する必要がある場合に非常に役立ちます。ドキュメントの詳細を追加するには、 [適用されたステップ] ペインでステップを右クリックし、[プロパティ]を選択します。

[ステッププロパティ]ダイアログ ボックスが表示され、フィルタリングまたは変換の理由を書き込むことができます。

クエリを整理して Power Query 言語フローを改善する

Power Query の最も一般的なベスト プラクティスの 1 つは、クエリを整理することです。パラメーター、関数、ステージング クエリ、およびデータ モデルにロードされるクエリ用のフォルダーを作成します。この例では、FileLocation クエリと DatesFrom クエリを選択し、右クリックします。次に、「グループに移動」を選択し、「新しいグループ」をクリックします。

次に、選択したクエリの名前を追加し、[OK]を押します。

クエリをグループ化すると、クエリ ペインは次のようになります。

すべてのステージング クエリについては、[読み込みを有効にする] のチェックを外して読み込みを無効にしてください。

このチュートリアルで説明するもう 1 つの点は、言語の流れです。「適用されたステップ」ペインの各ステップは、クリックすると表示される値を変換します。

初期データが取得され、ナビゲーション ステップで開始され、列が選択されました。次に、データ型を変更し、日付範囲を設定しました。列の名前も変更されました。

すべてのステップはテーブル タイプの値を返します。[詳細エディター] ウィンドウを開くと、 let式とin句が表示されます。それらの間には、式が割り当てられたステップまたは変数名のリストがあります。

クエリは、変数リストの最後のステップを参照するin句の後に続くものを返します。次に、 Mエンジンは、 in句からの依存関係チェーンをたどって不要なものをすべて削除し、可能であれば変換をソースにプッシュします。

Power Query 言語フローの概要

数式を見ると、クエリで実行されるステップの流れがわかります。各ステップのユーザー インターフェイスで使用される関数も表示されます。

クエリで行われた最初のステップは列の選択でした。ユーザー インターフェイスを使用してステップが実行されると、Table.SelectColumns関数が呼び出されます。最初のパラメータとして、前のステップの変数名を参照するテーブルを受け取りました。次に、選択したすべての列名がリストされます。

2 番目のステップでは、 Table.TransformColumnTypes関数を呼び出して列の型を変換しました。その最初のパラメータは、前のステップの出力を呼び出します。次に、一連の変換リストをリストしました。

3 番目のステップでは、 Table.SelectRows関数を使用して日付範囲フィルターを設定します。最初の引数としてテーブル タイプのクエリを受け取りました。この例では、「タイプの変更」ステップの出力を参照しています。

最後のステップでは、 Table.RenameColumns関数を使用して列の名前を変更しました。前のステップの出力が最初の引数として使用されました。次に、一連の名前変更リストをリストしました。

ユーザー インターフェイスを通じて適用されるすべての関数は、Table という単語で始まります。これらはすべてテーブル値を最初のパラメータとして受け取り、その値を変換しました。

各ステップが前のステップを参照しているため、コードは連続しているように見えますが、順序は必須ではありません。Mエンジンは常に依存関係チェーンに従うため、ステップを移動してもクエリは引き続き実行されます。

ユーザー インターフェイスがあると便利ですが、前の変換の結果を変換する必要があると常に想定されます。ほとんどの場合、これはおそらく当てはまりますが、当てはまらない場合は、コード内の参照値を手動で更新する必要があります。

Power Query データ型とコネクタ
LuckyTemplates データセット: 型と命名規則
データの読み込みと変換のベスト プラクティス

結論

データをグループ化して整理するには、データ変換が必要です。Power Query 言語フローの問題を簡単に追跡し、レポート内の変更を修正できるため、データ開発が迅速化されます。

メリッサ