Forum Discussion
Sequencing Numbering within a Group Based off Unique Values
I do not know if the most elegant solution. Yesterday I found an independent solution thru brute force.
1. Filter out Product Event that an End Date (Keep the nulls).
2. Concat Product Name and Program Name plus Start Date (converted date to a number) and Sorted. Earlier Dates would be higher up. (Product plus Date Number below)
3. Grouped by Products. (groups below)
4. Added costom column Rank = Table.AddRankColumn([Groups], "Rank", {"Product plus Date Number"}).
5. Then expanded grouped data.
6. Then filtered where Rank equal 1. In rare case where Product and Event had two rows were on the same day I could show.
Sorry If I was unclear.
I already filtered out rows if they had an end date.
For the remaining data, I was trying add increment/sequence numbering based dates in the Start Date column.
Watching this video. Might help. (9) Create Index Column By Group in Power Query - YouTube
Currently seeing if that will work.
| Product | Event | Start Date | End Date | Sequence Numbering |
| A | 2 | 1/5/2026 | 1 | |
| A | 3 | 1/7/2026 | 2 | |
| A | 4 | 1/9/2026 | 3 | |
| B | 2 | 2/9/2026 | 1 | |
| B | 3 | 2/9/2026 | 1 | |
| B | 4 | 2/14/2026 | 2 | |
| C | 1 | 3/15/2026 | 1 | |
| C | 2 | 3/16/2026 | 2 | |
| C | 3 | 4/1/2026 | 3 | |
| C | 4 | 4/2/2026 | 4 |
- Ashish_Mathur
Super User
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Product"}, {{"Count", each Table.AddRankColumn(_,"Sequence numbering",{"Start Date", Order.Ascending},[RankKind = RankKind.Dense])}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Event", "Start Date", "End Date", "Sequence numbering"}, {"Event", "Start Date", "End Date", "Sequence numbering"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"Product", type text}, {"Event", Int64.Type}, {"Start Date", type date}, {"End Date", type date}, {"Sequence numbering", Int64.Type}}) in #"Changed Type"Hope this helps.
