Power Query date time serial number

[Anonymous]

Hello,

I have a table that looks like this.

ID	DateTime
1	3-2-2020 08:00:38
1	3-2-2020 08:01:16
1	3-2-2020 08:01:23
2	3-2-2020 08:02:04
2	3-2-2020 08:04:43
3	3-2-2020 08:02:09
3	3-2-2020 08:02:11
3	3-2-2020 08:03:47
3	3-2-2020 08:04:36

I would like to add a column with increasing numbers per ID based on the date and time column. Below an example of the desired result.

ID	DateTime	Serialnr
1	3-2-2020 08:00:38	1
1	3-2-2020 08:01:16	2
1	3-2-2020 08:01:23	3
2	3-2-2020 08:02:04	1
2	3-2-2020 08:04:43	2
3	3-2-2020 08:02:09	1
3	3-2-2020 08:02:11	2
3	3-2-2020 08:03:47	3
3	3-2-2020 08:04:36	4

Is this possible with the help of power query and can someone help me with the right code? @Greg_Deckler  @MattAllington 

Thanks in advance!!

Kind regards,

Tiemen

[Mariusz · Accepted solution]

Hi @Anonymous 

You can use Group By on ID, All Rows as Operation 

And later adjust the Grouped Rows Step to accommodate Index column.

= Table.Group(#"Changed Type", {"ID"}, {{"tbl", each Table.AddIndexColumn( _, "Serialnr", 1, 1 ) , type table [DateTime=datetime, Serialnr=number]}})

Please see the attached file for reference.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi @Mariusz 

This works!! Thank you!! 🙂

Kind regards,

Tiemen