Forum Discussion
Sequencing Numbering within a Group Based off Unique Values
Hello,
Company I am at is decommissioning Alteryx.
Need some guidance to perform some sequence numbering in Power Bi/Power Query by group.
1. Filtering out products that already have an end date.
2. Sequence number based off a group and distinct dates.
In rare cases two events happen on the same date (B2 and B3 below). Need to keep.
So simple removal of duplicates would cause B3 to be removed.
Need to keep the rows that have a sequence number of 1 in example below.
Any recommendations?
| Product | Event | Start Date | End Date | Sequence Numbering |
| A | 1 | 1/2/2026 | 1/2/2026 | |
| A | 2 | 1/5/2026 | 1 | |
| A | 3 | 1/7/2026 | 2 | |
| A | 4 | 1/9/2026 | 3 | |
| B | 1 | 2/2/2026 | 2/3/2026 | |
| 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 |
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.
4 Replies
- JamieHolding
Resolver I
To rephrase your question...
You want to only keep entries where:
- There is no end date
- It's the earliest start date for a ProductIf that's true, you could try the following flag that would return 1 for rows you'd want to keep and 0 otherwise.
Flag_Earliest_NoEndDate = VAR CurrentProduct = SELECTEDVALUE(Products[Product]) VAR CurrentStartDate = SELECTEDVALUE(Products[StartDate]) VAR EarliestNoEndDate = CALCULATE( MIN(Products[StartDate]), FILTER( ALL(Products), Products[Product] = CurrentProduct && ISBLANK(Products[EndDate]) ) ) RETURN IF( ISBLANK(Products[EndDate]) && CurrentStartDate = EarliestNoEndDate, 1, 0 ) 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.
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.
