IDごとの最新バージョンの行全体を残す
製品ごとに最も大きいバージョン番号を求め、IDとバージョンで元の表に結合します。最新の状態や価格を、行ごと取り出せます。
最大のバージョン番号を求めるだけでは、その行の状態や価格は残りません。製品ごとの最大値を保存し、製品IDとバージョンの両方で元表に結び直すと、ほかの列も残せます。
最大値だけでは残らない情報
Versionsが架空の元表、Latestが第1段階で保存する集計結果です。Latestを手入力する手順ではありません。Q1の最大バージョン3には状態と価格が異なる2行があり、両方を残します。
Versions
| ProductID | Version | State | Price |
|---|---|---|---|
| Q1 | 1 | draft | 10 |
| Q1 | 3 | active | 14 |
| Q1 | 3 | review | 15 |
| Q2 | 2 | active | 20 |
Latest
| ProductID | LatestVersion |
|---|---|
| Q1 | 3 |
| Q2 | 2 |
最大値を保存して元表に結び直す
- Versionsだけを読み込み、Versionが数値型であることを確認します。文字列のままでは10と2の順序が変わる場合があります。
- 「結果を整理」を開いて「集計」を追加し、ProductIDをまとめる項目にします。Versionの「最大値」を選び、結果の項目名にLatestVersionと入力します。「適用」を押し、Q1=3、Q2=2の2行を確認します。この一覧を次の結合条件に使うため、Excelファイルに保存します。
- 新しい作業に元のVersionsと保存したLatestを読み込みます。前段階の集計を残したまま明細を取得しないでください。
- ProductID同士をつなぎ、さらにVersionとLatestVersionも結んで「一致する行のみ」を選びます。製品IDが同じで、バージョンがその製品の最大値と一致する明細だけが残ります。
- Latestの2列は目のアイコンで非表示にできます。元のState・Priceを含む次の3行が残ることを確認します。
最新バージョンの行が複数あれば、すべて残る
Q1のバージョン1は除かれ、バージョン3の2行が残ります。IDだけで結ぶと古いバージョンも戻るため、Versionの条件が必要です。Latestは製品ごとに1行にします。
| ProductID | Version | State | Price |
|---|---|---|---|
| Q1 | 3 | active | 14 |
| Q1 | 3 | review | 15 |
| Q2 | 2 | active | 20 |
必ず1行にしたい場合の注意
同じ最大バージョンの行が複数ある場合、どれか1行だけが自動で選ばれることはありません。1行に絞るには、更新時刻や記録IDなど、選ぶ基準を別に決める必要があります。また、この手順は数値のバージョン番号を対象としており、v1.2のような表記にはそのまま使えません。元データを更新したら、集計・保存・再読み込みからやり直してください。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
How do i filter out rows to only keep the highest number from a column in power query (Reddit r/excel)
原文は製品ごとに最も高いバージョンの行を、ほかの属性とともに残す方法を質問しています。 上の表と手順は、この質問を参考に別途作成した架空の例です。質問者の実データや製品の利用体験ではありません。
