Power Queryで重複する行を1行にまとめる方法｜グループ化で金額を合計し、複数の値をカンマ区切りで連結する

同じ請求番号や同じ取引先の行が何行もある明細を、キーごとに1行にまとめたいことがあります。Power Queryの「グループ化」を使うと、金額は合計し、品目や担当者などの文字列はカンマ区切りで1つのセルにつなげられます。

グループ化は複数の行を1行に畳む処理です。設定を誤ると、金額が合わなくなったり、列が消えたり、同じキーのはずの行が別々に残ったりします。この記事では、まとめ方の決め方、グループ化の設定、文字列の連結、まとめる前後の合計の照合までを順に説明します。

まとめた後の形を先に決める

例として、次のような明細を使います。

これを請求番号ごとに1行にまとめると、次のようになります。

グループ化では、キーにも集計にも指定しなかった列は結果から消えます。作業に入る前に、列ごとに扱いを決めておきます。

取引先をキーに加えるかどうかは、この表で決めておきます。1つの請求番号に必ず1つの取引先しかないなら、キーに加えても行数は変わりません。もし同じ請求番号が2行に分かれて出てきたら、取引先の表記が行によって違っているサインです。

まとめる前に整えておくこと

グループ化は、キーの値が完全に同じ行だけを1つにまとめます。見た目が同じでも、次のような違いがあると別のグループになります。

• 前後の空白（「A商事」と「A商事 」）
• 全角と半角（「INV-001」と「ＩＮＶ－００１」）
• 大文字と小文字（「inv-001」と「INV-001」）
• 見えない制御文字

キー列を選んで「変換」タブの「書式」から「トリミング」「クリーン」を実行しておくと、空白や制御文字による分かれは防げます。全角・半角や大文字・小文字の違いは、元データの入力ルールを確認するか、置換などでそろえてからグループ化します。

あわせて、金額列のデータ型を数値（10進数、または固定小数点数）にしておきます。テキスト型のままだと、合計の段階でエラーになります。

照合しやすくするため、整形までのクエリと、グループ化するクエリを分けておくと便利です。たとえば、トリミングと型変換までを「明細_整形」というクエリにし、それを右クリックして「参照」で作った新しいクエリでグループ化します。

グループ化で金額を合計する

1. 「明細_整形」を参照したクエリを開き、名前を「まとめ」などに変える
2. 「ホーム」タブ（または「変換」タブ）の「グループ化」を選ぶ
3. 「詳細設定」を選ぶ（キーや集計を複数指定するため）
4. グループ化の基準に「請求番号」を指定し、必要なら「グループ化の追加」で「取引先」も加える
5. 新しい列名を「金額合計」、操作を「合計」、列を「金額」にする
6. 「集計の追加」で、新しい列名「行数」、操作「行のカウント」を加える
7. もう1つ「集計の追加」で、新しい列名「品目一覧」、列「品目」を指定する。操作は仮に「最大」などを選んでおく
8. 「OK」を押す

画面の名称はExcelのバージョンによって少し違う場合があります。手順7で「最大」を選ぶのは仮の設定です。グループ化の画面には文字列を連結する操作がないため、次の手順で数式を書き換えます。

文字列をカンマ区切りで連結する

グループ化を実行すると、数式バーに次のような式が表示されます（ステップ名や型の書き方は環境によって異なります）。

= Table.Group(ソース, {"請求番号", "取引先"}, {{"金額合計", each List.Sum([金額]), type nullable number}, {"行数", each Table.RowCount(_), Int64.Type}, {"品目一覧", each List.Max([品目]), type nullable text}})

この中の List.Max([品目]) を Text.Combine([品目], ", ") に書き換えて確定します。

{"品目一覧", each Text.Combine([品目], ", "), type nullable text}

each の中で [品目] と書くと、そのグループに含まれる品目の値がリストとして渡されます。Text.Combine は、そのリストを第2引数の区切り文字でつないで1つの文字列にします。

目的別の書き方

補足です。

• Text.Combine に渡せるのはテキストのリストです。数値や日付の列をそのまま渡すと、型を変換できないという内容のエラーになります。Text.From で文字列に変えてから渡します。
• 連結する順番は、基本的に元の行の並びになります。順番に意味がある場合は、List.Sort で並べ替えるか、元データに連番の列を用意しておきます。
• 改行区切り（#(lf)）にした場合、Excelのシートでは「折り返して全体を表示する」を有効にしないと1行に見えることがあります。
• 最初の1件だけ残したい列（担当者など）は each List.First([担当者])、最新の日付を残したい列は each List.Max([日付]) のように集計を追加します。

詳細エディターでまとめて書く場合

整形とグループ化を1つのクエリで書くと、次のようになります。テーブル名「明細」はご自分のブックに合わせて読み替えてください。

let
    ソース = Excel.CurrentWorkbook(){[Name="明細"]}[Content],
    型変更 = Table.TransformColumnTypes(ソース, {{"請求番号", type text}, {"取引先", type text}, {"品目", type text}, {"金額", type number}}),
    トリミング = Table.TransformColumns(型変更, {{"請求番号", Text.Trim, type text}, {"取引先", Text.Trim, type text}}),
    グループ化 = Table.Group(トリミング, {"請求番号", "取引先"}, {
        {"金額合計", each List.Sum([金額]), type nullable number},
        {"品目一覧", each Text.Combine(List.Distinct([品目]), ", "), type nullable text},
        {"行数", each Table.RowCount(_), Int64.Type}
    })
in
    グループ化

ただし、次の照合を行うなら、前の手順のように「明細_整形」と「まとめ」の2つのクエリに分けておくほうが扱いやすくなります。

まとめる前後の合計が一致するか確かめる

グループ化した後は、少なくとも次の2点を確かめます。

• まとめる前の金額の合計と、まとめた後の「金額合計」の合計が一致する
• まとめる前の行数と、まとめた後の「行数」の合計が一致する

方法1：シートに読み込んで関数で比べる

「明細_整形」と「まとめ」の両方をテーブルとしてシートに読み込み、空いているセルで比べます。

=SUM(明細_整形[金額])
=SUM(まとめ[金額合計])
=ROWS(明細_整形)
=SUM(まとめ[行数])

テーブル名は、読み込んだときに付いた名前に合わせてください。

方法2：照合用のクエリを作る

「データの取得」から「空のクエリ」を作り、詳細エディターに次のように書きます。

let
    前の合計 = List.Sum(明細_整形[金額]),
    後の合計 = List.Sum(まとめ[金額合計]),
    前の行数 = Table.RowCount(明細_整形),
    後の行数 = List.Sum(まとめ[行数]),
    結果 = #table(
        {"項目", "まとめる前", "まとめた後", "一致"},
        {
            {"金額", 前の合計, 後の合計, Number.Round(前の合計 - 後の合計, 2) = 0},
            {"行数", 前の行数, 後の行数, 前の行数 = 後の行数}
        }
    )
in
    結果

金額に小数が含まれると、計算の誤差でごくわずかな差が出ることがあるため、差を丸めてから0と比べています。円単位の金額なら、金額列を固定小数点数の型にしておく方法もあります。

さらに、元のExcelシートやCSVの合計とも照合しておくと安心です。「明細_整形」の途中でフィルターやエラーの削除をしていると、整形後どうしの比較では一致していても、元データの行が抜けていることがあります。

一致しないときの確認先

照合で一致を確かめたら、「ホーム」タブの「閉じて読み込む」で結果をシートに出力します。元データを差し替えて更新したときも、照合用のクエリを一緒に更新すれば、毎回同じ確認ができます。