Power Query（M语言），删除重复项，如何实现保留最后一行

cqwcns

我发现power query删除重复项，默认是保留第一行的，而我希望保留最后一行。
我尝试先降序（日期列），再删除重复项，发现排序步骤对删除重复项是没有效果的。
以下代码，虽然操作了降序，但删除重复项仍是保留day1的。

1. let
   
2.     源 = Table.FromRecords({
   
3.     [day = 1, Name = "Bob", Phone = "123-4567"],
   
4.     [day = 1, Name = "Jim", Phone = "987-6543"],
   
5.     [day = 1, Name = "Paul", Phone = "543-7890"],
   
6.     [day = 1, Name = "Bob", Phone = "123-4567"],
   
7.     [day = 1, Name = "Jim", Phone = "987-6543"],
   
8.     [day = 2, Name = "Paul", Phone = "543-7890"],
   
9.     [day = 2, Name = "Bob", Phone = "123-4567"],
   
10.     [day = 2, Name = "Jim", Phone = "987-6543"],
    
11.     [day = 2, Name = "Paul", Phone = "543-7890"],
    
12.     [day = 2, Name = "Bob", Phone = "123-4567"],
    
13.     [day = 2, Name = "Jim", Phone = "987-6543"],
    
14.     [day = 3, Name = "Paul", Phone = "543-7890"],
    
15.     [day = 3, Name = "Bob", Phone = "123-4567"],
    
16.     [day = 5, Name = "Jim", Phone = "987-6543"],
    
17.     [day = 6, Name = "Paul", Phone = "543-7890"],
    
18.     [day = 6, Name = "Bob", Phone = "123-4567"],
    
19.     [day = 6, Name = "Jim", Phone = "987-6543"],
    
20.     [day = 6, Name = "Paul", Phone = "543-7890"]
    
21.     }),
    
22.     排序的行 = Table.Sort(源,{{"day", Order.Descending}}),
    
23.     删除的副本 = Table.Distinct(排序的行, {"Name"})
    
24. in
    
25.     删除的副本

复制代码

所以，一般这种需求如何实现？
请大佬指教，感谢。

sayhi95

逆序表，删除重复项，再逆序表

624134745

分组-table.last-展开

cqwcns

加个buffer可以实现需求，什么原理不了解

1. let
   
2.     源 = Table.FromRecords({
   
3.     [day = 1, Name = "Bob", Phone = "123-4567"],
   
4.     [day = 1, Name = "Jim", Phone = "987-6543"],
   
5.     [day = 1, Name = "Paul", Phone = "543-7890"],
   
6.     [day = 1, Name = "Bob", Phone = "123-4567"],
   
7.     [day = 1, Name = "Jim", Phone = "987-6543"],
   
8.     [day = 2, Name = "Paul", Phone = "543-7890"],
   
9.     [day = 2, Name = "Bob", Phone = "123-4567"],
   
10.     [day = 2, Name = "Jim", Phone = "987-6543"],
    
11.     [day = 2, Name = "Paul", Phone = "543-7890"],
    
12.     [day = 2, Name = "Bob", Phone = "123-4567"],
    
13.     [day = 2, Name = "Jim", Phone = "987-6543"],
    
14.     [day = 3, Name = "Paul", Phone = "543-7890"],
    
15.     [day = 3, Name = "Bob", Phone = "123-4567"],
    
16.     [day = 5, Name = "Jim", Phone = "987-6543"],
    
17.     [day = 6, Name = "Paul", Phone = "543-7890"],
    
18.     [day = 6, Name = "Bob", Phone = "123-4567"],
    
19.     [day = 6, Name = "Jim", Phone = "987-6543"],
    
20.     [day = 6, Name = "Paul", Phone = "543-7890"]
    
21.     }),
    
22.     排序的行 = Table.Buffer(Table.Sort(源,{{"day", Order.Descending}})),
    
23.     删除的副本 = Table.Distinct(排序的行, {"Name"})
    
24. in
    
25.     删除的副本

复制代码

fengbao008

排序之后一般加个TABLE.BUFFER，原因我也不清楚。

suiyuanban

fengbao008 发表于 2022-11-21 09:44
排序之后一般加个TABLE.BUFFER，原因我也不清楚。

PQ的排序是一种虚浮的状态，不是真实的排序，计算机内部仍然是原来的顺序，加一个TABLE.BUFFER，把排序结果装入计算机缓存，固化了排序结果，也能提高运算速度，个人理解。

fengbao008

suiyuanban 发表于 2022-11-21 10:34
PQ的排序是一种虚浮的状态，不是真实的排序，计算机内部仍然是原来的顺序，加一个TABLE.BUFFER，把排序结 ...

应该是这样的。

quqiyuan

是不是反转行的意思。我看结果一致，供参考！
let
    源 = Table.FromRecords({
    [day = 1, Name = "Bob", Phone = "123-4567"],
    [day = 1, Name = "Jim", Phone = "987-6543"],
    [day = 1, Name = "Paul", Phone = "543-7890"],
    [day = 1, Name = "Bob", Phone = "123-4567"],
    [day = 1, Name = "Jim", Phone = "987-6543"],
    [day = 2, Name = "Paul", Phone = "543-7890"],
    [day = 2, Name = "Bob", Phone = "123-4567"],
    [day = 2, Name = "Jim", Phone = "987-6543"],
    [day = 2, Name = "Paul", Phone = "543-7890"],
    [day = 2, Name = "Bob", Phone = "123-4567"],
    [day = 2, Name = "Jim", Phone = "987-6543"],
    [day = 3, Name = "Paul", Phone = "543-7890"],
    [day = 3, Name = "Bob", Phone = "123-4567"],
    [day = 5, Name = "Jim", Phone = "987-6543"],
    [day = 6, Name = "Paul", Phone = "543-7890"],
    [day = 6, Name = "Bob", Phone = "123-4567"],
    [day = 6, Name = "Jim", Phone = "987-6543"],
    [day = 6, Name = "Paul", Phone = "543-7890"]
    }),
    逆序的行 = Table.ReverseRows(源),
    删除的副本 = Table.Distinct(逆序的行, {"Name", "Phone"})
in
    删除的副本

quqiyuan

是不是反转行的意思，我看结果一致，供参考！

let
    源 = Table.FromRecords({
    [day = 1, Name = "Bob", Phone = "123-4567"],
    [day = 1, Name = "Jim", Phone = "987-6543"],
    [day = 1, Name = "Paul", Phone = "543-7890"],
    [day = 1, Name = "Bob", Phone = "123-4567"],
    [day = 1, Name = "Jim", Phone = "987-6543"],
    [day = 2, Name = "Paul", Phone = "543-7890"],
    [day = 2, Name = "Bob", Phone = "123-4567"],
    [day = 2, Name = "Jim", Phone = "987-6543"],
    [day = 2, Name = "Paul", Phone = "543-7890"],
    [day = 2, Name = "Bob", Phone = "123-4567"],
    [day = 2, Name = "Jim", Phone = "987-6543"],
    [day = 3, Name = "Paul", Phone = "543-7890"],
    [day = 3, Name = "Bob", Phone = "123-4567"],
    [day = 5, Name = "Jim", Phone = "987-6543"],
    [day = 6, Name = "Paul", Phone = "543-7890"],
    [day = 6, Name = "Bob", Phone = "123-4567"],
    [day = 6, Name = "Jim", Phone = "987-6543"],
    [day = 6, Name = "Paul", Phone = "543-7890"]
    }),
    逆序的行 = Table.ReverseRows(源),
    删除的副本 = Table.Distinct(逆序的行, {"Name", "Phone"})
in
    删除的副本