Forum Discussion

kalkhudary's avatar
kalkhudary
Helper IV
3 years ago
Solved

Column index based on year

Hello Community,    I am trying to create an index column that starts from 1, 2, ... based on my date column where the index column will show 1 for all dates in 2017  and 2 for all dates in 2018, e...
Need Help
Show and Tell
  • HotChilli's avatar
    HotChilli
    3 years ago

    Get the year from the Date column using Date.Year()  and subtract 2016

PhilipTreacy's avatar
PhilipTreacy
Super User
3 years ago

Hi kalkhudary 

 

Just chipping in my 2c as I can do it in less steps 🙂

 

As HotChilli describes you just need the year - 2016.  Whilst jbwtp 's code gives the result you want, you just need to add a single custom column

 

#"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each Date.Year([Date]) - 2016)

 

 

Full code here 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7bCcQwEEPRXvwdkGb8iF2LSf9tLBvL8XweBBfNmRzMcNqdnmsmA+tRJtgOfYA9sMMYeMM8sIIv+2IDS2BVWSwqi1ll0VUWqfJYNJXHt7ZD25/F/Vncn0V9dn4sgWWV/3x+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Index", each Date.Year([Date]) - 2016)
in
    #"Added Custom"

 

 

 

I'd feel bad if you mark this as the solution, as I'm just expanding on Hot Chili's answer. 

 

Regards

 

Phil