Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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
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
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
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
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 ?
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.
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 |
Check out the July 2025 Power BI update to learn about new features.