Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.