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

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

5 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    3 years ago

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

    • jbwtp's avatar
      jbwtp
      Memorable Member
      3 years ago

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