行のグループ化と集計

Power Query では、1 つ以上の列の値に従って行をグループ化することによって、さまざまな行の値を単一値にグループ化できます。 次の 2 種類のグループ化操作から選択できます。

  • 集計関数を使用して列を集計します。

  • 行操作を実行します。

このチュートリアルでは、次の画像に示されているサンプル テーブルを使用します。

サンプルの初期テーブル。

[Year] (2020)、[Country] (米国、パナマ、またはカナダ)、[Products] (シャツまたはショーツ)、[Sales Channel] (オンラインまたはリセラー)、[Units] (55 から 7500 までのさまざまな値) を示す列を含むテーブル :::image-end:::

[グループ化] ボタンの場所

[グループ化] ボタンは、次の 3 つの場所で見つけることができます。

  • [ホーム] タブの [変換] グループで。

    [ホーム] タブの [グループ化]。

  • [変換] タブの [テーブル] グループで。

    [変換] タブの [グループ化]。

  • 列を選択して右クリックしたときのショートカット メニューで。

    ショートカット メニューの [グループ化]。

集計関数を使用して 1 つ以上の列でグループ化する

この例での目標は、国と販売チャネルのレベルで販売された合計ユニット数を集計することです。 [Country] および [Sales Channel] 列を使用してグループ化操作を実行します。

  1. [ホーム] タブで [グループ化] を選択します。
  2. 複数の列を選択してグループ化できるように、 [詳細] オプションを選択します。
  3. [Country] および [Sales Channel] 列を選択します。
  4. [新しい列] セクションで、名前が [Total units] 、集計操作が [合計] 、使用される列が [Units] である新しい列を作成します。
  5. [OK] をクリックします。

集計された列を含む [グループ化] ダイアログ ボックス。

この操作により、求めていたテーブルが表示されます。

集計された列を含むサンプル出力テーブル。

行操作を実行して 1 つ以上の列でグループ化する

この例では、販売された合計ユニット数に加えて、国と販売チャネルのレベルで集計された、最も売れている製品の名前と販売されたユニット数を示す他の 2 つの列が必要です。

目標は、元のサンプル テーブルから、次の画像のようなテーブルに到達することです。

行操作を使用したサンプル出力テーブル。

  1. [グループ化] 列として次の列を使用します。

    • Sales Channel
  2. 次の操作を実行して、2 つの新しい列を作成します。

    1. [合計] 操作を使用して [Units] 列を集計します。 この列に「Total units」という名前を付けます。
    2. [すべての行] 操作を使用して、新しい [Products] 列を追加します。

    非集計列を含む [グループ化] ダイアログ ボックス。

その操作が完了したら、 [Products] 列の各セル内に [[テーブル]] 値がどのように含まれているかに注意してください。 各 [[テーブル]] 値には、元のテーブルの [Country] および [Sales Channel] 列でグループ化されたすべての行が含まれています。 セル内の空白を選択することにより、ダイアログ ボックスの下部にそのテーブルの内容のプレビューを表示できます。

テーブルの詳細プレビュー ウィンドウ。

注意

詳細プレビュー ウィンドウに、グループ化操作に使用された一部の行が表示されないことがあります。 [[テーブル]] 値を選択することにより、対応するグループ化操作に関連するすべての行を表示できます。

次に、新しい [Products] 列内にあるテーブルの [Units] 列の値が最も大きな行を抽出し、その新しい列に「Top performer product」という名前を付ける必要があります。

最も売れている製品の情報を抽出する

[[テーブル]] 値を含む新しい [Products] 列を使用して、リボンの [列の追加] タブに移動し、 [全般] グループから [カスタム列] を選択することによって新しいカスタム列を作成します。

カスタム列を追加する。

この新しい列に「Top performer product」という名前を付けます。 [カスタム列の式] に数式 Table.Max([Products], "Units" ) を入力します。

Table.Max を含むカスタム列の式。

その数式の結果、[[レコード]] 値を含む新しい列が作成されます。 これらのレコード値は、基本的には 1 行だけを含むテーブルです。 これらのレコードには、 [Products] 列内の各 [[テーブル]] 値の [Units] 列の最大値を含む行が含まれています。

Table.Max を含むカスタム列の式の結果。

[[レコード]] 値が含まれているこの新しい [Top performer product] 列を使用して、expand. 展開アイコンを選択し、 [Products] および [Units] フィールドを選択してから [OK] を選択できます。

[Top performer product] 列のレコード値の展開操作。

[Products] 列を削除し、新しく展開された両方の列のデータ型を設定すると、結果は次の画像のようになります。

すべての変換を含む最終的なテーブル。

あいまいグループ化

注意

次の機能は Power Query Online でのみ使用できます。

"あいまいグループ化" を実行する方法を示すには、次の画像に示されているサンプル テーブルを検討してください。

Miguel と William という名前のさまざまなスペルと大文字/小文字が含まれている 9 行のエントリを含むテーブル。

あいまいグループ化の目標は、テキスト文字列に対して近似一致アルゴリズムを使用するグループ化操作を実行することです。 Power Query では、Jaccard 類似性アルゴリズムを使用して、インスタンスのペア間の類似性を測定します。 次に、凝集型階層クラスタリングを適用してインスタンスをグループ化します。 次の画像は、テーブルが [Person] 列でグループ化されている、予測される出力を示しています。

![[Person] と [Frequency] がそれぞれ、"Miguel" と "Mike"、3 と 2 であるエントリを示すテーブル。](images/me-fuzzy-grouping-sample-final-table-no-transform-table.png "[Person] と [Frequency] がそれぞれ、"Miguel" と "Mike"、3 と 2 であるエントリを示すテーブル")

あいまいグループ化を実行するには、この記事で前に説明したのと同じ手順を実行します。 唯一の違いは、今回は [グループ化] ダイアログ ボックスで [あいまいグループ化を使用する] チェック ボックスをオンにすることです。

[グループ化] ダイアログ ボックスのあいまいグループ化のチェック ボックス。

Power Query では、行のグループごとに、最も頻度の高いインスタンスを "正規の" インスタンスとして選択します。 複数のインスタンスが同じ頻度で発生する場合、Power Query では最初のものが選択されます。 [グループ化] ダイアログ ボックスで [OK] を選択すると、予測していた結果が表示されます。

あいまいグループ化のサンプルの最終的なテーブル、変換テーブルはなし。

ただし、 [あいまいグループオプション] を展開することによって、あいまいグループ化操作をより細かく制御できます。

あいまいグループオプション。

あいまいグループ化では、次のオプションを使用できます。

  • [類似性のしきい値 (省略可能)] : このオプションは、類似した 2 つの値をどのようにグループ化する必要があるかを示します。 最小の設定である 0 を指定すると、すべての値がグループ化されます。 最大の設定である 1 を指定すると、完全に一致する値のグループ化のみが許可されます。 既定値は 0.8 です。
  • [大文字と小文字を区別しない] : テキスト文字列を比較するときに、大文字と小文字の区別が無視されます。 このオプションは、既定で有効です。
  • [テキストの一部を結合してグループ化する] : このアルゴリズムでは、テキストの一部を結合して (MicrosoftMicrosoft に結合するなど) 値をグループ化しようとします。
  • [変換テーブル (省略可能)] : グループ化するために値をマップする (MSFTMicrosoft へのマッピングなど) 変換テーブルを選択できます。

この例では、値をどのようにマップできるかを示すために変換テーブルが使用されます。 この変換テーブルには、次の 2 つの列があります。

  • [From] : テーブル内で検索するテキスト文字列。
  • [To] : [From] 列のテキスト文字列を置き換えるために使用するテキスト文字列。

次の画像は、この例で使用されている変換テーブルを示しています。

mike と William という [From] の値と Miguel と Bill という [To] の値を示すテーブル。

重要

変換テーブルの列と列名が上に示されているものと同じであることが重要です (これらは [From] と [To] である必要があります)。そうでない場合、Power Query ではこれらを認識できません。

[グループ化] ダイアログ ボックスに戻り、 [あいまいグループオプション] を展開してから、 [変換テーブル] ドロップダウン メニューを選択します。

あいまいグループ化のサンプルの [変換テーブル] ドロップダウン メニュー。

変換テーブルを選択した後、 [OK] を選択します。 その操作の結果、次の画像に示されている結果が表示されます。

変換テーブルを含む、あいまいグループ化のサンプルの最終的なテーブル。

この例では、 [大文字と小文字を区別しない] オプションが有効になっていたため、文字列の大文字と小文字を考慮せずにテキスト文字列を検索するために [変換テーブル][From] 列の値が使用されます。 まず、この変換操作が実行されてから、あいまいグループ化操作が実行されます。

注意

複数の列でグループ化しているときに、値の置き換えによって類似性スコアが高くなる場合は、すべての列の置き換え操作が変換テーブルによって実行されます。

関連項目

カスタム列を追加する
重複を削除する