shikumika’s diary

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

Power QueryでExcelのCOUNTIF関数のように、各行の値の重複を数える方法(List編)

前回、Power Queryで「ExcelのCOUNTIF関数」のように各行の値の重複を数える方法として、グループ化の機能を使用した方法を紹介しました。今回はListを使った方法です。

 

ただし、前提として、今回の方法は使用できる場面が少ない印象です。

大量データには不向きなので、その場合は前回の「グループ化」を使う方法が最適と思います。

shikumika.org

 

今回の方法だと数千件程度まででないと、処理時間がとても長くなる印象です。

(私の環境で、3000件のサンプルデータで試したところ、グループ化なら1秒程度の計算時間が、今回の方法で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を使う場面で、データ量が少ないケースはそれほどないと思いますが。