Forum Discussion

Fraistan's avatar
Fraistan
Icon for Helper I rankHelper I
3 years ago

Re-index every year

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. 

3 Replies

  • BIswajit_Das's avatar
    BIswajit_Das
    Icon for Impactful Individual rankImpactful Individual

    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's avatar
      Fraistan
      Icon for Helper I rankHelper I

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

  • 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.