grouping / filtering of recurring data by chronological order

[psvdr]

Dear Community,

I'm asking for your kind help for an M-code soulution for the following:

The goal is the get the first occurance of recurring values while keeping them in chronological order.

Maybe it's just an easy grouping that I can't see.

input:                                   rows to keep:

datetime	value
2024-04-16 11:40:41	2	<-
2024-04-16 11:56:42	2
2024-04-16 23:12:01	18	<-
2024-04-17 20:10:31	18
2024-04-17 20:43:19	18
2024-04-17 21:02:03	2	<-
2024-05-04 06:12:11	18	<-
2024-05-05 11:14:20	5	<-
2024-05-07 03:10:14	18	<-
2024-05-07 19:50:51	5	<-
2024-05-09 08:02:08	5

expected output:

datetime	value
2024-04-16 11:40:41	2
2024-04-16 23:12:01	18
2024-04-17 21:02:03	2
2024-05-04 06:12:11	18
2024-05-05 11:14:20	5
2024-05-07 03:10:14	18
2024-05-07 19:50:51	5

Many thanks in advance.

[bhanu_gautam]

@psvdr Use below M-code

let
// Load your data
Source = ...,

// Add an Index column to keep track of the original order
AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

// Group by the 'value' column and keep the first occurrence within each group
Grouped = Table.Group(AddIndex, {"value"}, {{"FirstOccurrence", each Table.FirstN(_, 1)}}),

// Expand the grouped table to get the first occurrence rows
Expanded = Table.ExpandTableColumn(Grouped, "FirstOccurrence", {"datetime", "value", "Index"}),

// Sort by the Index column to restore the original order
Sorted = Table.Sort(Expanded, {{"Index", Order.Ascending}}),

// Remove the Index column as it's no longer needed
RemoveIndex = Table.RemoveColumns(Sorted, {"Index"})
in
RemoveIndex

[psvdr · Accepted solution]

Thank you so much for your efforts, that was super quick.

However, the solution that I was looking for is well described in another post.

[v-dineshya]

Hi @psvdr ,

 Thanks for the update and happy to know you have found a solution. You can simply post the details of your approach and mark it 'Accept as Solution'. It may help others who may be in similar scenarios.

Thank you.