Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 |
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |