Power BI上で重複行を、条件付き(値の修正、合算)しながらrawデータを整理したい。

Question

Power BIで業務をデータを集計しています。

添付のようなデータ(約2000行)をPower BIに入れ加工、可視化したいと考えているのですが、重複行の作業が複雑で行き詰ってしまい質問させて頂きました。

具体的には添付ファイルの上の絵のようなデータを下の絵のように整理したいと思っています。

自分が考えた作業の方向性は下記です。
前提：重複行が無ければ何もしない。
重複行に遭遇した時、
①重複行であることを認識する。
②重複行のうち片方の行にしか数字が無い場合　→数字がある方の値をセル内に残す。
両方の列に数字がある場合　→購入日が新しい方を残す。
③不要な行を削除し、１行にする。
④完了
⑤次の行に移行する。　※重複行が３行以上ある場合もあり、①に戻り作業を開始する。
最終的に、すべて１行で表現したいと考えています。

ご助言を頂けましたら幸いです。

Comments

[sk.exe]

> 前提：重複行が無ければ何もしない。
> 重複行に遭遇した時、
> ①重複行であることを認識する。

まずここでおっしゃっている「重複行」というのが、具体的にどのような条件や
パターンに当てはまるグループ（ 1 件以上のレコードの集合）であるのかを
次のような形で明確に定義して下さい。

・[購入者ID]（および[顧客名]）の値が同じであるグループ

・全てのレコードを[インデックス]の昇順に並べ替えた状態において、
　[購入者ID]（および[顧客名]）の値が同じであるレコードが
　1 件以上連続しているグループ

例えばもし 16 件目以降に[購入者ID]の値が 300101 である
（ 1 件目で出現済みの[購入者ID]と同じである）レコードが存在する場合、
前者と後者ではグループ化の範囲が明らかに異なってきます。

もし前者であるなら、とりあえず[購入者ID]と[顧客名]でグループ化して
グループごとに[インデックス]の最小値を求めるクエリを作るところから
始めることになるはず。

[hirasawonder]

コメント頂き感謝申し上げます。
重複の定義は下記でございます。

・[購入者ID]（および[顧客名]）の値が同じであるグループ
→おっしゃる通りです。[購入者ID]と[顧客名]は必ずイコールです。例外はありません。

・全てのレコードを[インデックス]の昇順に並べ替えた状態において、
　[購入者ID]（および[顧客名]）の値が同じであるレコードが
　1 件以上連続しているグループ
→おっしゃる通りです。[購入者ID]（および[顧客名]）の値が同じであるレコードが２件以上並んでいる場合、重複している、とみなします。

例えばもし 16 件目以降に[購入者ID]の値が 300101 である
（ 1 件目で出現済みの[購入者ID]と同じである）レコードが存在する場合、
前者と後者ではグループ化の範囲が明らかに異なってきます。
→ありがとうございます。もし 16 件目以降に[購入者ID]の値が 300101 である（ 1 件目で出現済みの[購入者ID]と同じである）レコードが出現する可能性はあります。
こちら説明不足でした。ご指摘感謝申し上げます。

大変お手数ではございますが、ご助言を賜れましたら幸いです。
何卒よろしくお願い申し上げます。

[sk.exe]

> [購入者ID]（および[顧客名]）の値が同じであるレコードが
> ２件以上並んでいる場合、重複している、とみなします。

つまり[購入者ID]の値が同じレコード同士であっても、
それらの間に[購入者ID]の値が異なるレコードが挟まっていれば
「シーケンスが異なる」としてみなして別々にピックアップ
しなければならない、という解釈でよろしいでしょうか。

> もし 16 件目以降に[購入者ID]の値が 300101 である
> （ 1 件目で出現済みの[購入者ID]と同じである）レコードが
> 出現する可能性はあります。

例えば [インデックス]の値が 16 であるレコードの[購入者ID]の値が 300101 である場合、
[インデックス]の値が 1 であるレコードとは[購入者ID]の値が同じであっても
シーケンスが異なるので、1 の方も 16 の方も両方抽出しなければならない、
ということになるはず。
もしそうなら、単純に「[購入者ID]ごとにグループ化する」というわけにはいきません。

> 重複行のうち片方の行にしか数字が無い場合　→数字がある方の値をセル内に残す。
> 両方の列に数字がある場合　→購入日が新しい方を残す。

> ※重複行が３行以上ある場合もあり

上記の作業だとか、そもそも「『正しい購入』と『誤った購入』を
どうやって判別するのか」といった点に関しては正直機械的に
（少なくともPowerQueryの範疇で）処理できる根拠を見い出せないのですが、
とりあえずやるべきことは「それぞれのシーケンスごとに先頭の
（[インデックス]の値が最小である、あるいは[購入日]の値が最大である）
レコードを抽出するクエリを作成すること」なのではないかと。

[hirasawonder]

再度ご検討をお時間を頂き、本当に感謝申し上げます。

> [購入者ID]（および[顧客名]）の値が同じであるレコードが
> ２件以上並んでいる場合、重複している、とみなします。

つまり[購入者ID]の値が同じレコード同士であっても、
それらの間に[購入者ID]の値が異なるレコードが挟まっていれば
「シーケンスが異なる」としてみなして別々にピックアップ
しなければならない、という解釈でよろしいでしょうか。
→ありがとうございます。シーケンスは同じとみなして一括りにしてピックアップしても大丈夫、でございました。

> もし 16 件目以降に[購入者ID]の値が 300101 である
> （ 1 件目で出現済みの[購入者ID]と同じである）レコードが
> 出現する可能性はあります。

例えば [インデックス]の値が 16 であるレコードの[購入者ID]の値が 300101 である場合、
[インデックス]の値が 1 であるレコードとは[購入者ID]の値が同じであっても
シーケンスが異なるので、1 の方も 16 の方も両方抽出しなければならない、
ということになるはず。
もしそうなら、単純に「[購入者ID]ごとにグループ化する」というわけにはいきません。
→ありがとうございます。もし16であるレコードの[購入者ID]の値が 300101 である場合、[インデックス]の値が 1 であるレコードと同じシーケンスとみなして一括りにして抽出する、で大丈夫でございました。

> 重複行のうち片方の行にしか数字が無い場合　→数字がある方の値をセル内に残す。
> 両方の列に数字がある場合　→購入日が新しい方を残す。

> ※重複行が３行以上ある場合もあり

上記の作業だとか、そもそも「『正しい購入』と『誤った購入』を
どうやって判別するのか」といった点に関しては正直機械的に
（少なくともPowerQueryの範疇で）処理できる根拠を見い出せないのですが、
とりあえずやるべきことは「それぞれのシーケンスごとに先頭の
（[インデックス]の値が最小である、あるいは[購入日]の値が最大である）
レコードを抽出するクエリを作成すること」なのではないかと。
→ありがとうございます。詳しくは書いていないのですが、ここまでお話しいただいた条件を満たせれば、『正しい購入』と『誤った購入』を判別することはできると考えておりました。
ここまでお話しいただいた条件で、Power Queryの書き方をお教えて頂くことは可能でしょうか。

大変お手数ではございますが、何卒よろしくお願い申し上げます。

Answer 1

わたしも同じ悩みでした。
PowerBIだとメジャーをつくってみたら解決できそうです。

ぶどう購入　 = LASTNONBLANKVALUE('元データ '[購入者ID], MAX('元データ'[ぶどう購入]))

この場合、購入者IDでフィルタリングして、ぶどう購入の値が一番大きいものを返します。
合計にしたい場合は、上の式のMAXをSUMにしてみるといいと思います。

Answer 2

使用するデータについて

とりあえず、次のようなCSVファイル（Shift JISエンコード）を使用するものと仮定します。

インデックス,購入日（時間順）,購入者ID,顧客名,ぶどう購入,もも購入,すいか購入,バナナ購入
1,2022/10/28 13:32,300101,高尾さん,,5,,
2,2022/10/27 10:37,519800,佐藤さん,,1,,
3,2022/10/25 16:44,519800,佐藤さん,4,,,2
4,2022/10/26 14:57,837100,山本さん,,2,1,
5,2022/10/25 14:50,837100,山本さん,,,1,
6,2022/10/24 14:43,157960,髙橋さん,,2,,
7,2022/10/19 16:18,157960,髙橋さん,,1,,
8,2022/10/28 11:06,421330,徳永さん,,1,,
9,2022/10/23 18:20,302560,芦田さん,5,4,1,
10,2022/10/23 17:18,302560,芦田さん,5,4,,
11,2022/10/28 12:59,131860,久保さん,,,2,
12,2022/10/15 20:10,131860,久保さん,1,2,1,
13,2022/10/16 18:10,788960,鈴木さん,1,1,,1
14,2022/10/16 18:05,788960,鈴木さん,1,1,,
15,2022/10/16 18:00,788960,鈴木さん,,,,2
16,2022/10/16 15:11,300101,高尾さん,3,,,1

1) データソースの読み込み

まず上記のCSVファイルを読み込むクエリ[LoadCsv]を作成します。

let
    Source = Csv.Document(File.Contents("C:\FolderName\FileName.csv"),[Delimiter=",", Encoding=932, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"インデックス", Int64.Type}, {"購入日（時間順）", type datetime}, {"購入者ID", type text}, {"顧客名", type text}, {"ぶどう購入", Int64.Type}, {"もも購入", Int64.Type}, {"すいか購入", Int64.Type}, {"バナナ購入", Int64.Type}})
in
    ChangedType

File.Contents 関数に渡すファイルパスは、実際のものに合わせて適宜修正して下さい。

2) カスタム関数の作成

重複行のうち片方の行にしか数字が無い場合　→数字がある方の値をセル内に残す。
両方の列行に数字がある場合　→購入日が新しい方を残す。

次に、上記のルールに則り[LoadCsv]から任意の購入者IDに該当するレコードを抽出し、任意の集計項目から最適な数値を得るための関数[OptimizedPurchaseCount]を作成します。

let GetValue = (CustomerId as text, EvaluatedColumnName as text) =>
    let 
        Source = LoadCsv,
        SelectedCustomer = Table.SelectRows(Source, each [購入者ID] = CustomerId),
        UnpivotedOtherColumns = Table.UnpivotOtherColumns(SelectedCustomer, {"インデックス", "購入日（時間順）", "購入者ID", "顧客名"}, "集計項目", "個数"),
        RejectedNull = Table.SelectRows(UnpivotedOtherColumns, each [集計項目] = EvaluatedColumnName and [個数] <> null),
        SortedByPurchaseDate = Table.Sort(RejectedNull, {{"購入日（時間順）", Order.Descending},{"インデックス", Order.Ascending}}), 
        FirstValue = List.First(SortedByPurchaseDate[個数])
    in
        FirstValue
in
    GetValue

3) 購入者ごとにレコードを1件ずつに絞り込む

更に次のようなクエリを作成します。

let
    Source = LoadCsv,
    GroupedRows = Table.Group(Source, {"購入者ID", "顧客名"}, {{"インデックス", each List.Min([インデックス]), type nullable number}}),
    MergedQueries = Table.NestedJoin(GroupedRows, {"インデックス"}, Source, {"インデックス"}, "LoadCsv", JoinKind.LeftOuter),
    ExpandedColumns = Table.ExpandTableColumn(MergedQueries, "LoadCsv", {"購入日（時間順）"}, {"購入日"}),
    ReorderedColumns = Table.ReorderColumns(ExpandedColumns,{"インデックス", "購入日", "購入者ID", "顧客名"}),
    GetPurchaseCountOfGrapes = Table.AddColumn(ReorderedColumns, "ぶどう購入", each OptimizedPurchaseCount([購入者ID], "ぶどう購入")),
    GetPurchaseCountOfPeaches = Table.AddColumn(GetPurchaseCountOfGrapes, "もも購入", each OptimizedPurchaseCount([購入者ID], "もも購入")),
    GetPurchaseCountOfWatermelons = Table.AddColumn(GetPurchaseCountOfPeaches, "すいか購入", each OptimizedPurchaseCount([購入者ID], "すいか購入")),
    GetPurchaseCountOfBananas = Table.AddColumn(GetPurchaseCountOfWatermelons, "バナナ購入", each OptimizedPurchaseCount([購入者ID], "バナナ購入"))
in
    GetPurchaseCountOfBananas

（クエリのプレビュー）

Comments

[hirasawonder]

ありがとうございます！御礼のコメントを送ったつもりが送れていませんでした、申し訳ございません。レベルが高くまだ最後までたどりつけていないですが、だいぶ全体像を理解することができました。本当に感謝申し上げます。