Get last status of item in Power Query (mcode)

[thangdev]

Dear everyone, 

I have a query as below table 

ID	Item
1	A
2	A
3	A
4	B
5	B
6	C
7	C
8	C
9	D
10	D
11	D

I would like to add additional column to select last row of each item , something like below table, could you please assist for this ? 

ID	Item	select
1	A	no
2	A	no
3	A	yes
4	B	no
5	B	yes
6	C	no
7	C	no
8	C	yes
9	D	no
10	D	no
11	D	yes

[slorin]

Hi @thangdev 

Another solution

= Table.FromColumns(
  Table.ToColumns(Your_Source) &
      {List.Transform(
            List.Zip({Your_Source[Item], List.Skip(Your_Source[Item])}),
            List.IsDistinct)},
  type table [ID = Int64.Type, Item = text, Select = logical]
)

Stéphane 

[thangdev]

Hello @slorin 

Thank you for your assist, however I tried with your code, the result is also incorrect if the column "ID" is inorder , please see below picture for more details

[dufoq3 · Accepted solution]

Hi @thangdev,

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjKCs4zhLBMgywnMMoWzzIAsZzDLHM6ygLMsgSwXMMvQAME0hDBjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Item = _t]),
    GroupedRows = Table.Group(Source, {"Item"}, {{"All", each 
        [ a = Table.AddIndexColumn(_, "IndexHelper",0,1),
          b = Table.AddColumn(a, "Select", (x)=> if x[IndexHelper] = List.Max(a[IndexHelper]) then "yes" else "no", type text),
          c = Table.RemoveColumns(b, {"IndexHelper"})
        ][c], type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

[thangdev]

hello @dufoq3 ,

I read the code, it seems that you added the index column then go row by row to check, that is correct with this instance, however If the ID column isn't order, does this code work correctly ? 

[dufoq3]

Hi, yes this code groups by [Item] and add internal index column. Then it check whether current internal row [IndexHelper] equals max of current group [IndexHelper] and if yes then "yes" else "no".

It will check row by row with respecting of last step row order.

[thangdev]

Hi @dufoq3 ,

Thank you for your reply.  

I understood your solution, but it is only correct if the column ID was order. in the different situation, It's wrong, let consider below example, I have tested and It is not my expectation 

ID	Item
1	A
100	A
3	A
4	B
5	B
38	C
7	C
8	C
9	D
15	D
11	D