前回、Power Queryで「ExcelのCOUNTIF関数」のように各行の値の重複を数える方法として、グループ化の機能を使用した方法を紹介しました。今回はListを使った方法です。
なお、以下は縦方向のカウントで当該行にあるデータが列全体で何件あるかをカウントする方法です。横方向のカウントのサンプルは、指定した値と同じ値の列の個数を行ごとにカウントする方法に記載。
また、縦方向のカウントの場合の前提として、今回の方法は使用できる場面が少ない印象です。
今回の方法はレコード数が数千件程度まででないと、処理時間がとても長くなりそうです。
（私の環境で、3000件のサンプルデータで試したところ、グループ化なら１秒程度の計算時間が、今回の方法で30秒ほどかかり、処理時間が大きく異なりました）
大量データには不向きなので、その場合は前回の「グループ化」を使う方法が最適と思います。
アウトプットイメージ
前回と同様、「名前」という列にある人と同じ名前の件数を「カウント」に表示するイメージ
下図で、「名前のカウント」部分をクエリで集計します。
作成手順
「テーブルまたは範囲から」で、クエリの作成
次のような、顧客ID、名前、都道府県、年代などのデータがあり、名寄せチェックのために「名前」の重複カウントをパワークエリで数えたい、とします。
「カスタム列」の追加
上記の実施後、次のクエリの画面が表示されるので「カスタム列」をクリック
新しい列名は、適当な名前を入力し、カスタム列の式には次のような式を入力します。
=List.Count(List.FindText(list as list, text as text))
具体的には、今回の場合は、
- list部分： 一つ前のステップ名である”変更された型”と、重複を数える対象の列名
- text部分： 重複を数える対象の列名
を使って、以下を追加する。
List.Count(List.FindText(変更された型[名前], [名前]))
「名前」の重複カウントが追加されます。
他の方法との違い
グループ化による方法との違い
現状のPower Query（パワークエリ）で、各行の値の重複件数を数える方法は次の「グループ化」が最適と思われる。
理屈は不明で推測でしかないですが、今回の方法は、レコード毎にList.FindText(変更された型[名前]を計算して、List.Count関数で計算することになる印象。
（Excelで大量データ時にするCOUNTIF関数も遅いので）
Power Queryを使う場面で、データ量が少ないケースはそれほどないと思いますが、データ量が少ない時に適していそうです。
特定の列と同じ値の列の個数を行ごとにカウントする方法
「当該行にあるデータが列全体で何件あるかをカウント」ではなく、「特定の列と同じ値の列の個数を行ごとにカウントする方法」の場合は次の方法です。
参考情報
サンプルデータ
上記の記事をもとに作成したサンプルファイルを以下で公開しています。実際のファイルで確認したい場合はご覧ください。
データの加工・抽出、管理の操作事例
データの加工・抽出、管理の操作事例を以下にまとめています。
なお、Power Queryについては、次の書籍で何ができるのか全体像が理解できると思います。
以上、Power QueryでExcelのCOUNTIF関数のように、各行の値の重複を数える方法（List編）でした。
