Column index based on year

[kalkhudary]

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, etc... Can someone help guide me step by step how to reach to this result?

Currently I can created an index column in the transform and it shows each row as a number but need now to show all dates in 2017 as 1 and all in 2018 as 2 and all in 2019 as 3 etc...

[kalkhudary]

@jbwtp @HotChilli @PhilipTreacy  Thanks all for this tremendous support. All syntax worked.

[PhilipTreacy]

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

[kalkhudary]

@HotChilli Can you help show and tell the steps? 

[HotChilli · Accepted solution]

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

[jbwtp]

Or more generalised:

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}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year"}, {{"Data", each _, Value.Type(#"Changed Type")}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Year"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Date"}, {"Date"})
in
    #"Expanded Data"