Re-index every year

[Fraistan]

Hi, 

I got this sample data: 

I want to re-index every year. 

So if I have 4 tests in 2019, I want the index to count: 1, 2, 3, 4.
In 2020 we have 2 tests, I want the power query to count: 1, 2
And so on. 

[danextian]

You can use Table.Group function to achieve what you want. Be warned that this is similar to looping in other  programing languages so this can be very slow with large tables. Here's a sample M script. Paste this to an empty/blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDdCoMwDIXfpddC027qfBbxIsxZSbrNMZjQp1+0P8gYDb04X3NO0r5XYLSUBWtUpdBtRw1VrwxosFnf5dvqCMl5jLzRcCqcGD3HRjhraIrhe5yiKjFtUZnQzV7u1U2lqSt4lCD2vKCfOMS0VstAmfvJ4cwRGG3KmEEsrzIHYcncmf1ZrTvof1aDWkslTsRjkmXjS5b9PTwwf1QcbZPDk5acbUoGi3fyaFP0aXvMxD4TWx8I0/JC/qhh+AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, TestValue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"TestValue", type text}}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Date", Order.Ascending}})),
    #"Inserted Year" = Table.AddColumn(#"Sorted Rows", "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year"}, {{"Grouped", each _, type table [Date=nullable date, TestValue=nullable text, Year=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Grouped], "Index", 1)),
    #"Expanded Index" = Table.ExpandTableColumn(#"Added Custom", "Index", {"Date", "TestValue", "Index"}, {"Date", "TestValue", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Index",{"Grouped"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"TestValue", type text}, {"Index", Int64.Type}})
in
    #"Changed Type1"

Also, going forward please post a sample data that can be easily copy-pasted, not an image.

[BIswajit_Das]

Hello @Fraistan 
I think there isn't any way to change the index value but
you can use another column as index and assign respected values year wise and use that column
Thanks.

[Fraistan]

Unfourtanly no 😞 Need to have a new index (start from 1) on each job for each year.