[[20220124160641]]　『パワークエリを利用した優先条件を付けての重複の』（えん）　ページの最後に飛ぶ

『パワークエリを利用した優先条件を付けての重複の削除』（えん）

下記のような社内アンケートの回答状況の集計を行っています。
元データはCSVです。

実際には多くの列があり集計にパワークエリを利用しています。

やりたいこととしては社員番号の重複の削除、
ただし、最終更新時刻ではなく
回答終了＞回答中断＞未回答の優先順序でデータを残したいのです。

回答終了後にアンケートシステムにログインすると再度回答ができてしまうため、
その後の回答中断もしくは未回答を重複業として削除できればと思っております。

A列 B列 C列 D列
社員番号ユーザー名部門回答状況
051360 A太郎総務部回答終了
050612 B二郎経理部回答中断
050621 C三郎未回答未回答
050591 D四朗経理部回答終了
050591 D四朗経理部回答中断
050621 C三郎人事部回答中断
050612 B二郎経理部回答終了

グループ化して整序、条件列を追加して......最終的に重複業の削除というようなイメージをしておりますが、うまくいきません。

どうぞよろしくお願いいたします。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 パット見で思いつく心配を一つ。
 ＞ 回答終了＞回答中断＞未回答の優先順序でデータを残したいのです。
 ＞ 回答終了後にアンケートシステムにログインすると再度回答ができてしまうため、

 上記から考えるに、二回目の解答終了など同じ回答状況の重複があった場合はどれを残すか決まっていますか？
 それとも発生しえない、と思ってよろしいのでしょうか。
（.:*.ゆ ゅ） 2022/01/24(月) 16:23

＞.:*.ゆゅ様

早々にありがとうございます。
そのケースは発生しうるのですが
結論としては同じ回答状況の場合はどれが残っても結構です。

（えん） 2022/01/24(月) 16:33

並べ替えたとき、回答終了＞回答中断＞未回答　となるよううに、
条件列を追加してはどうでしょうか。

（マナ） 2022/01/24(月) 17:25

＞マナ様

ありがとうございます。
回答終了なら1、回答中断なら2のように番号を振って整序するイメージですか？

（えん） 2022/01/25(火) 10:36

 ざっくりイメージですが。

 元
     |[A]     |[B]     
 [1] |社員番号|状況    
 [2] |  123456|未回答  
 [3] |  122222|回答中断
 [4] |  111111|未回答  
 [5] |  345678|未回答  
 [6] |  222222|回答終了
 [7] |  240524|回答中断
 [8] |  122222|未回答  
 [9] |  111111|回答終了
 [10]|  345678|回答終了
 [11]|  111111|回答中断
 [12]|  345678|回答終了

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    追加された条件列 = Table.AddColumn(ソース, "カスタム", each if [状況] = "回答終了" then 1 else if [状況] = "回答中断" then 2 else 3),
    並べ替えられた行 = Table.Buffer(Table.Sort(追加された条件列,{{"カスタム", Order.Ascending}})),
    削除された重複 = Table.Distinct(並べ替えられた行, {"社員番号"})
in
    削除された重複

 変換後
    |[A]     |[B]     |[C]     
 [1]|社員番号|状況    |カスタム
 [2]|  111111|回答終了|       1
 [3]|  222222|回答終了|       1
 [4]|  345678|回答終了|       1
 [5]|  240524|回答中断|       2
 [6]|  122222|回答中断|       2
 [7]|  123456|未回答  |       3
（.:*.ゆ ゅ） 2022/01/25(火) 11:27

 .:*.ゆ ゅ さんの回答をみて、なるほど Table.Buffer 使う必要があるのがわかりました
 重複の削除で残る行が一定しないなぁとおもっていろいろ試してましたが、
 いろいろ試した結果がこちらです
　
 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"社員番号", type text}, {"ユーザー名", type text}, {"部門", type text}, {"回答状況", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "カスタム", each List.PositionOf({"回答終了","回答中断","未回答"}, [回答状況])),
    グループ化された行 = Table.Group(追加されたカスタム, {"社員番号"}, {{"カウント", each List.Min([カスタム]), type number}}),
    マージされたクエリ数 = Table.NestedJoin(追加されたカスタム,{"カスタム", "社員番号"},グループ化された行,{"カウント", "社員番号"},"クエリ1",JoinKind.Inner),
    削除された列 = Table.RemoveColumns(マージされたクエリ数,{"カスタム", "クエリ1"})
 in
    削除された列
（´・ω・`） 2022/01/25(火) 13:35

 グループ化したものをマージという発想、いいですね。マージが苦手なもので全く思いつきませんでした。
 SortとBufferはデータ量によって重くなりそうなので（未確認）
 （´・ω・`）さんのほうがいいかもしれないですね
（.:*.ゆ ゅ） 2022/01/25(火) 16:15

Table.Bufferの説明は、わたしには、ここが一番わかりやすかったです。
https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/

あと、インデックス列の追加という回答も、いくつか見かけました。
https://social.technet.microsoft.com/Forums/ja-JP/00a09332-fe6e-47a4-a8a9-b71e38b01a86/how-to-remove-duplicates-based-on-sort-order?forum=powerquery

（マナ） 2022/01/25(火) 20:01

 マナさん、情報提供ありがとうございます。
 Table.Bufferはその性質上やはり重くなるという理解で問題なさそうですね。
 インデックス列を追加するとソートが保存されるのは、上の方の記事の
 ＞Power Query also uses a concept called “lazy evaluation”.
  In short this means that if you add a step in the code, and that step is not technically needed to produce the final result, then that step is actually never executed 
 (even though it is there in the instructions)
 ここの影響なんでしょうね。勉強になりました。
（.:*.ゆ ゅ） 2022/01/26(水) 09:45

皆様ありがとうございました。
Table.bufferを使った方法、実際にはどこからの設定になるのでしょうか。
詳細エディタから行うしかないのでしょうか。

理解が浅く申し訳ないです。
（えん） 2022/01/26(水) 16:11

 そうですね、もしくは数式バーから変更可能です。
 マナさんが紹介してくださった記事の上のほうに操作手順も画像付きでありますから、それを参照するとわかりやすいと思います。
（.:*.ゆ ゅ） 2022/01/26(水) 17:50

＞.:*.ゆゅ様

ご返信ありがとうございます。
ページも拝見したのですが、英語ページで少し読み進めるのに苦労していました。
少しずつ読み解いてみようと思います。
（えん） 2022/01/27(木) 13:55

コメント返信：

[ 一覧(最新更新順) ]

[[20220124160641]] 『パワークエリを利用した優先条件を付けての重複の』（えん） ページの最後に飛ぶ

[[20220124160641]]　『パワークエリを利用した優先条件を付けての重複の』（えん）　ページの最後に飛ぶ