重複を消す前に、どの値が何件あるのかを確かめたいことがあります。たとえば、取引先コードが二重に登録されていないか、同じ注文番号が複数の行にないかを見る場合です。
Power Queryでは、グループ化でキー列ごとに行数を数えます。そのうえで件数が2以上のキーだけを残すと、重複の一覧ができます。さらに、その結果を元の明細にマージすると、重複しているのがどの行なのかも分かります。
この記事では、元データはそのまま残し、次の3つのクエリを作ります。
| クエリ名（例） | 中身 | 用途 |
|---|---|---|
| 明細 | 元データをそのまま読み込んだもの | ほかのクエリの元 |
| 件数 | キーごとの件数（2件以上だけ） | 重複キーの一覧 |
| 重複明細 | 重複キーを持つ元の行と件数 | どの行が重複かの確認 |
準備：元データを読み込み、キー列を決める
- 元データの表を選び、［データ］タブの［テーブルまたは範囲から］でPower Queryエディターを開きます。CSVの場合は［テキストまたはCSVから］で読み込みます。
- 右側の［クエリの設定］で、クエリ名を「明細」に変えます。
- 重複かどうかを判定するキー列を決めます。1列で決まらないときは、取引日と伝票番号のように複数の列を組み合わせてキーにします。
- キー列のデータ型を確認します。「001」のようなコードが数値型になっていると、先頭の0が消えて「001」と「1」が同じ値として数えられることがあります。コードは原則としてテキスト型にします。
- ［列の追加］→［インデックス列］で連番を付けておくと、あとで元の並び順に戻せます。
手順1：「明細」を参照して件数用のクエリを作る
左のクエリ一覧で「明細」を右クリックし、［参照］を選びます。新しくできたクエリの名前は「件数」にします。
［参照］で作ったクエリは「明細」の結果から始まるため、元のクエリの手順を変えずに集計できます。［複製］を使うと手順がまるごとコピーされるので、取り込み手順を直すときに両方を直す手間が生じます。
手順2：グループ化でキーごとの行数を数える
- 「件数」クエリを開き、［ホーム］タブまたは［変換］タブの［グループ化］を選びます。
- ［基本］のまま、グループ化する列としてキー列を選びます。
- 新しい列名を「件数」、操作を［行数のカウント］にして［OK］を押します。
キーが複数列のときは［詳細設定］に切り替え、［グループ化の追加］で2列目以降を加えます。
数式バーには次のような式が入ります。ステップ名や列名は環境によって変わります。
= Table.Group(ソース, {"取引先コード"}, {{"件数", each Table.RowCount(_), Int64.Type}})
複数列のキーでは、2つ目の引数に {"取引日", "伝票番号"} のように列名が並びます。
ここまでで、キーごとに1行ずつ、その値が何行あったかの表ができます。
手順3：絞り込む前に、件数の合計で検算する
グループ化で行が漏れていなければ、次の関係が成り立ちます。
- 「件数」列の合計 ＝ 「明細」の行数
「件数」列を選び、［変換］タブの［統計］→［合計］を押すと合計値が表示されます。確認できたら、追加されたそのステップを右側の［適用したステップ］で削除し、次の手順に進みます。シートに読み込んでからSUM関数で合計し、「明細」の行数と比べてもかまいません。
合わないときは、「明細」側の途中で行を除いていないか（フィルターやエラー行の削除など）を見直します。
手順4：件数が2以上のキーだけを残す
- 「件数」列の見出しの▼を押し、［数値フィルター］→［指定の値以上］を選びます。
- 値に「2」を入れて［OK］を押します。
- 必要に応じて、「件数」列を降順で並べ替えます。件数の多いキーが上に並ぶので、どこから調べるか決めやすくなります。
この操作で、次のようなステップが追加されます。
= Table.SelectRows(グループ化された行, each [件数] >= 2)
［適用したステップ］で1つ前のステップをクリックすると、すべてのキーの件数表をいつでも確認できます。
手順5：元の明細に件数を戻し、重複している行を見る
重複キーの一覧だけでは、どの行を確認すればよいかが分かりません。元の明細とマージして、重複している行そのものを並べます。
- 「明細」をもう一度［参照］し、名前を「重複明細」にします。
- ［ホーム］→［クエリのマージ］を開きます。
- 上の表に「重複明細」、下の表に「件数」（2件以上に絞ったもの）を選びます。
- 両方の表で同じキー列をクリックして選びます。複数列のキーでは、Ctrlキーを押しながら同じ順番で選びます。
- 結合の種類を［内部（一致する行のみ）］にして［OK］を押します。
- 追加された列の見出しにある展開ボタンを押し、「件数」だけにチェックを入れて展開します。
- キー列で並べ替えると、同じキーの行が隣り合います。元の順番に戻したいときは、準備で付けたインデックス列で並べ替えます。
内部結合なので、件数が2以上のキーを持つ行だけが残ります。すべての行に件数を付けたいときは、絞り込む前の件数表（すべてのキー）を用意し、結合の種類を［左外部］にします。
マージする相手には、必ずグループ化した後の表を使ってください。グループ化した表ではキーが1行ずつなので、マージしても明細の行数は増えません。元の明細どうしをマージすると、重複キーの行の組み合わせの数だけ行が増えてしまいます。
1つのクエリで済ませる方法
クエリを分けたくない場合は、グループ化の［詳細設定］で集計を2つ作ります。
- 「件数」：操作は［行数のカウント］
- 「行一覧」：操作は［すべての行］
= Table.Group(ソース, {"取引先コード"}, {{"件数", each Table.RowCount(_), Int64.Type}, {"行一覧", each _, type table}})
続けて「件数」を2以上で絞り込み、「行一覧」列を展開すると、重複している元の行が件数付きで並びます。展開するときにキー列のチェックを外しておけば、同じ列が重なりません。手順は短くなりますが、件数の一覧と明細の一覧を別々のシートに読み込みたいときは、手順5のようにクエリを分けたほうが扱いやすくなります。
件数が思ったとおりにならないときの確認
| 症状 | 考えられる原因 | 確認方法 |
|---|---|---|
| 見た目は同じ値なのに別々に数えられる | 前後の空白、全角と半角、大文字と小文字の違い | キー列を複製し、［変換］→［書式］の［トリム］［小文字］などをかけた列でグループ化して件数を比べる |
| 「001」と「1」が同じキーになる | キー列が数値型になっている | データ型をテキストにしてから取り込み直す |
| 空欄の行が大量の重複に見える | キーが空（null）の行が1つのグループにまとまっている | nullの行は別に数え、重複とは分けて扱う |
| 空欄なのに2つのグループに分かれる | nullと空文字（""）が混ざっている | 列のフィルターで（null）と（空白）を見比べる |
| マージした後に行数が増えた | マージ相手にキーの重複がある | 下の表にグループ化した後の表を選んでいるか確認する |
Power Queryのグループ化は、標準では大文字と小文字を区別して比べます。どこまでを同じ値とみなすかは業務のルールによって決まります。まず比較用の列で件数がどう変わるかを確かめてから決めると安全です。
読み込み先と更新
- 「件数」と「重複明細」は、［閉じて次に読み込む］でそれぞれ新しいシートにテーブルとして読み込みます。
- 「明細」をほかのクエリの元として使うだけなら、［接続の作成のみ］にしておくとシートが増えません。
- 元データが変わったら、［データ］→［すべて更新］を押すと、件数と重複明細がまとめて更新されます。
チェックリスト
- キー列（1列か複数列か）を決めた
- キー列のデータ型を確認した（コードはテキスト型）
- 元のクエリは［参照］で残し、直接書き換えていない
- グループ化の操作が［行数のカウント］になっている
- 件数の合計が元の行数と一致した
- 件数2以上で絞り込んだ
- マージ相手がグループ化した後の表で、明細の行数が増えていない
- 空白、大文字と小文字、nullの違いで分かれているキーがないか確認した
